DAX Power BI Conditional filtering

1
votes

Sample file

The column c of a table T can have the values 1, 2 or 3. I want to filter the table such as when the user selects value 1 nothing is filtered. When the selected value is 2 then show only the rows with the c column containing the value 2 or the value 3 not the value 1 and finally if the selected value is 3 then show only those rows containing 3 in the c column. The slider or a plain filter must be single selection not multi because otherwise it would violate one of the user's business rule.

Selected    Show
    1     all rows
    2     rows with 2 or 3
    3     only rows with 3

I tried to create columns and to create measures but I can't get anywhere. Any directions?

enter image description here

2
powerbidax

2 Answers

2
votes

I agree that the disconnected table might be the best workaround here. I hope this solution will work with your actual file.

Step 1 - create a new disconnected table

FilterC = DISTINCT(T[c])

enter image description here

Step 2 - make sure that your slicer is sourced from the new table (FilterC)

enter image description here

Step 3 - create a measure that will return 1 for rows that we want to see, and 0 for rows that we want to hide:

mFilter = 
    var Filter_C = SELECTEDVALUE(FilterC[c])
    var Row_C = SELECTEDVALUE(T[c])
return
    SWITCH(
        Filter_C,
        "1", 1,
        "2", IF(OR(Row_C = "2", Row_C = "3"), 1, 0),
        "3", IF(Row_C = "3", 1, 0)
    )

Step 4 - add this measure to the table, or even table filters will suffice.

enter image description here

Step 5 - start switching the values!

enter image description here

enter image description here

enter image description here

1
votes

First you need to set it to a number column.

enter image description here

Second, you could write complex measures and use a disconnected table, but the best option is to use the correct slicer. There is a Greater than or Equal To option.

enter image description here