Filter by different column depending on user selection

[Mahonia]

I have a table with several Boolean columns and I need a user selector (either a slicer or a filter) to vary which of these columns is used to filter the visuals.

The data looks like this:

 Dimension, OptionA, OptionB, OptionC

Mem1, TRUE, FALSE, FALSE

Mem2, TRUE, FALSE, TRUE

Mem3, FALSE, FALSE, FALSE

I need a user selector that would look like this:

Select Option

OptionA

OptionB

OptionC

Then, when the user selects OptionA, the visualisation should be based only on those members which are TRUE in column OptionA. 

This would be very easy to do in Tableau using parameters, calculated fields and filters, but I have not found a way to do this in Power BI. If I use a parameter for the selector, this is not available to users in Power BI Service, so this is not useful. 

It looks like I will need to add the selector list as a new table, and then build logic using ISFILTERED( ). If I put that logic in a calculated Column it doesn't work as the column doesn't update when the filter is changed; but if I put that logic in a calculated Measure then it can't be used a filter on the visualisations. 

Does anyone have any ideas for how this logic could be built?

[v-shex-msft · Accepted solution]

HI @Mahonia,

You can refer to below steps to achieve your requirement.

1. Add a selector table as the source of slicer.

Selector = DATATABLE("Select",string,{{"OptionA"},{"OptionB"},{"OptionC"}})

2. Add a measure to get the value of selected option.

Tag = 
IF (
    SWITCH (
        SELECTEDVALUE ( Selector[Select] ),
        "OptionA", LASTNONBLANK ( 'SampleData'[OptionA], [OptionA] ),
        "OptionB", LASTNONBLANK ( 'SampleData'[OptionB], [OptionB] ),
        "OptionC", LASTNONBLANK ( 'SampleData'[OptionC], [OptionC] )
    ),
    "Y",
    "N"
)

3. Add visual level filter on above measure to filter tag "Y" records.

Result:

Regards,

Xiaoxin Sheng

[Mahonia]

Hi @v-shex-msft, thank you very much for this, this works perfectly as long as the visualisation has this dimension included.

However, if I want to do a Distinct Count of members of the dimension, the filter on the Tag measure does not work, because it evaluates at the top level. Do you know of any way around this?

[v-shex-msft]

 Hi @Mahonia,

For your scenario, I'd like to suggest you to use RLS on to apply the filter effect on top level.
You can refer to below links to know more about RLS:

Power BI – Dynamic Row Level Security – Tips to get it working!

Tutorial: Dynamic row level security with Analysis services tabular model

Regards,

Xiaoxin Sheng