Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |