Forum Discussion

ExcelMonke's avatar
ExcelMonke
Icon for Impactful Individual rankImpactful Individual
2 years ago
Solved

Filtering using Switch / IF

I have a large data table with a column that returns two values based on an IF statement.   IF true THEN "Default" ELSE "Not Default"   What I would like to do is to create a slicer that on a si...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ExcelMonke 

    You can refer to the following solution

    Sample data

     

    1.You can create a slicer table 

     

    2.Create a measure and put it to the visual filter

    Measure =
IF (
    ISFILTERED ( 'Slicer'[Column1] ),
    IF (
        OR (
            SELECTEDVALUE ( 'Slicer'[Column1] ) = "default"
                && SELECTEDVALUE ( 'Table'[Column2] ) = "default",
            SELECTEDVALUE ( 'Slicer'[Column1] ) = "all"
        ),
        1,
        0
    ),
    1
)

     

     

    Output

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

ExcelMonke's avatar
ExcelMonke
Icon for Impactful Individual rankImpactful Individual
2 years ago

I don't think I was explaining what I was hoping for clearly. 
I want two single-click options:

  1. Filters to all "Default" values
  2. Return all values are returned that are both "Default" AND "Not Default"

i.e. one option to select default values, one option to effectively "select all"

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi ExcelMonke 

You can refer to the following solution

Sample data

 

1.You can create a slicer table 

 

2.Create a measure and put it to the visual filter

Measure =
IF (
    ISFILTERED ( 'Slicer'[Column1] ),
    IF (
        OR (
            SELECTEDVALUE ( 'Slicer'[Column1] ) = "default"
                && SELECTEDVALUE ( 'Table'[Column2] ) = "default",
            SELECTEDVALUE ( 'Slicer'[Column1] ) = "all"
        ),
        1,
        0
    ),
    1
)

 

 

Output

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.