Forum Discussion

andy_scott42's avatar
andy_scott42
Icon for Helper II rankHelper II
7 years ago
Solved

Slicer - Single selection filters two values in table

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.

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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