PowerBI DAX Switch & SelectedValue

Question

I'm experiencing an issue with a DAX code.

When I write the code as follows, I don't get any errors.

FilteredTableByRiskType =
**VAR SelectedRisk = "A"**
VAR FilteredTable =
FILTER(DB_Table,
        SWITCH(SelectedRisk ,
            "A", DB_Table[A]=1 ,
            "B", DB_Table[B] =1,
            "C", DB_Table[C]=1 ,
            "D", DB_Table[D] =1,
                        FALSE()
        )
)
RETURN
FilteredTable

However, if I write it like this, it returns an empty table. I'm also checking the SelectedRisk value in a table, and I'm able to retrieve it correctly.

FilteredTableByRiskType =
**VAR SelectedRisk = SELECTEDVALUE('Risk_Table'[Risk])**
VAR FilteredTable =
FILTER(DB_Table,
        SWITCH(SelectedRisk ,
            "A", DB_Table[A]=1 ,
            "B", DB_Table[B] =1,
            "C", DB_Table[C]=1 ,
            "D", DB_Table[D] =1,
                        FALSE()
        )
)
RETURN
FilteredTable

I also tried the following code for checking, and it returns "True."

IsTest = SELECTEDVALUE(Risk_Table[Risk])  = "A"

What can I do? Thank you.

I tried different methots.

Answer 1

Perhaps you are needing a Measure, try:

SelectedRiskType = 
  var selectedRisk = SELECTEDVALUE('Risk_Table'[Risk])
  var filterTable = FILTER(DB_Table,
        SWITCH(selectedRisk ,
            "A", DB_Table[A]=1 ,
            "B", DB_Table[B] =1,
            "C", DB_Table[C]=1 ,
            "D", DB_Table[D] =1,
                        FALSE()
        )
  )
  RETURN CALCULATE(COUNTROWS(DB_Table), filterTable)

This measure will return the number of rows matching your criteria, add this measure to your visual and/or add it to the visual filter pane.

Answer 2

Your second table expression, using SELECTEDVALUE, returns no rows since that column probably has more than one value in it.

Unless you use the optional second parameter in SELECTEDVALUE, the function returns a blank if the referred column does not contain one single value.

The table expression does not take slicer selection into account when it calculates, which is why the part you are looking at evaluates correctly when used as a measure in a visual.

Note that you are trying to persist a calculated table to your data model - you cannot have a persisted table updated at query time (i.e. when you make slicer selections), calculated tables are only evaluated at processing time (i.e. when the report data is refreshed).

You can probably solve whatever you are attempting using measures instead.