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 that I need to filter by a slicer but the slicer value needs to select multiple criteria.
Below is my table and my slicer.
If a room has Gender of Both then it can be male or female. So when I select Male in the slicer I want it to select rows where the Gender is Male or Both. Likewise for female. (Example for expected Female selection is below)
So my logic needs to be:
Male selected = Male and Both
Female selected = Female and Both
Both selected = Both
How can I achieve this? I tried setting up a bridging table but can't get it work.
Thanks in advance.
Solved! Go to Solution.
Thanks Natel.
I used this solution and created an intermediate table https://community.powerbi.com/t5/Desktop/One-selection-in-slicer-filtering-two-values/td-p/489524
@andy_scott42 - If all you want is the list, you could add a column "mapping" and duplicate the rows for the "Both" rooms. Create the following Calculated Table to see the pattern. You will filter on the "mapping" column and show the "Value" and "Gender" columns in the table.
aTest = UNION( ADDCOLUMNS( GENERATESERIES(1,10), "Gender", SWITCH(TRUE(),[Value]<4,"Both",[Value]<6,"Male","Female"), "Mapping", SWITCH(TRUE(),[Value]<4,"Male",[Value]<6,"Male","Female") ), ADDCOLUMNS( GENERATESERIES(1,3), "Gender", "Both", "Mapping", "Female" ), ADDCOLUMNS( GENERATESERIES(1,3), "Gender", "Both", "Mapping", "Both" ) )
Alternatively, you could create a disconnected parameter table, and add a Measure that will return blank if the required condition is not met. Here is the Parameters Calculated Table:
Parameters = DATATABLE( "GenderParm", STRING, {{"Both"},{"Female"},{"Male"}} )
And here is the Measure. You can add this to your table visual and make the column width so narrow that it will be hidden.
Compare Lists = var a = values(Parameters[GenderParm]) var b = values(aTest[Gender]) var c = FILTER(CROSSJOIN(a,b), OR([GenderParm] = [Gender], [Gender] = "Both")) return COUNTROWS(c)
Cheers!
Nathan
For the 2nd solution above, the slicer will be from the Parameters table.
Also, I forgot the simplest solution: Add 2 new columns to your table: "Males Included" and "Females Included". Then have 2 slicers instead of 1.
Thanks Natel.
I used this solution and created an intermediate table https://community.powerbi.com/t5/Desktop/One-selection-in-slicer-filtering-two-values/td-p/489524
@andy_scott42 - ah, that is a good solution. 🙂 Also, could do a many-to-many relationship and skip the bridge table.
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |