Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Mahonia
New Member

Filter by different column depending on user selection

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?

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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"}})

6.PNG

 

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.

7.PNG

 

Result:

8.PNG9.PNG

 

Regards,

Xiaoxin Sheng

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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"}})

6.PNG

 

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.

7.PNG

 

Result:

8.PNG9.PNG

 

Regards,

Xiaoxin Sheng

Hi @Anonymous, 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?

Anonymous
Not applicable

 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

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.