Slicer - Single selection filters two values in table

[andy_scott42]

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.

[Anonymous]

@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

[Anonymous]

@andy_scott42 -

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.

[andy_scott42 · Accepted 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

[Anonymous]

@andy_scott42  - ah, that is a good solution. 🙂 Also, could do a many-to-many relationship and skip the bridge table.