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 data table with several columns that you want to use to create a slicer, where if the selected value is found in any of such columns, then such a line has to appear.
For example, I want to create a slicer with a list Key of options "A", "B" & "C".
When the user selects any or multiple items from the slicer such line items containing 1 in those respective columns have to appear.
If the user selects/ticks slicer options A & B, then A, D, F, & G rows should appear.
https://1drv.ms/u/s!An03iU493hAgnpwhuu6i7Rs59CXiRw?e=6NZnKv
Can someone help me how to achieve this?
Solved! Go to Solution.
Hi @klllmmm ,
Here are the steps you can follow:
1. Power Query – Copy Table1 to form Table2..
2. Select – [Key_A], [Key_B], [Key_C] – Unpivot Columns.
Result:
3. Create column calculated.
Slicer table =
DISTINCT('Table2'[Attribute])
4. Create measure.
Flag =
var _select=SELECTCOLUMNS('Slicer table',"1",[Attribute])
return
IF(
MAX('Table'[Category]) in
SELECTCOLUMNS(FILTER(ALL('Table2'),'Table2'[Attribute] in _select),"2",[Category]) ,1,0)
5. Place [Flag]in Filters, set is=1, apply filter.
6. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @klllmmm ,
The SELECTCOLUMN function refers to saving the returned results to form a table, where "2" is a column name of the table, you can choose any name.
Refer to:
SELECTCOLUMNS function (DAX) - DAX | Microsoft Learn
IF(
MAX('Table'[Category]) in
SELECTCOLUMNS(FILTER(ALL('Table2'),'Table2'[Attribute] in _select),"2",[Category]) ,1,0)
This function represents:
When the data in column [Category] exists in column [2] formed using the SELECTCOLUMN function, 1 is returned, otherwise 0.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @klllmmm ,
Here are the steps you can follow:
1. Power Query – Copy Table1 to form Table2..
2. Select – [Key_A], [Key_B], [Key_C] – Unpivot Columns.
Result:
3. Create column calculated.
Slicer table =
DISTINCT('Table2'[Attribute])
4. Create measure.
Flag =
var _select=SELECTCOLUMNS('Slicer table',"1",[Attribute])
return
IF(
MAX('Table'[Category]) in
SELECTCOLUMNS(FILTER(ALL('Table2'),'Table2'[Attribute] in _select),"2",[Category]) ,1,0)
5. Place [Flag]in Filters, set is=1, apply filter.
6. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you so much for this answer!!!
One more thing.
In the step no 4, SELECTCOLUMN function what is referred from "2" ?
Such problems have been solved on this forum countless times. Please use the Search box to find them. Also, on YT there are vids (some by Alberto Ferrari) that show how to use a slicer to get the OR semantics instead of AND (because this is what you're actually asking for).
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |