Forum Discussion

ExcelMonke's avatar
ExcelMonke
Impactful 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 single click:

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

I've been playing around with Switch and IF functions and am not able to figure this one out. Any help is appreciated!

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.

     

6 Replies

  • rsbin's avatar
    rsbin
    Community Champion
    2 years ago

    ExcelMonke ,

    I think you're making this more complicated than need be.

    Simply create a Slicer Visual and drag this column.  This will act as your Slicer

    Be sure to turn on the Select All option:

    Will this solve your issue?

    Regards,

    • ExcelMonke's avatar
      ExcelMonke
      Impactful Individual
      2 years ago

      Thanks for the suggestion - I am actually hoping to do a single select option rather than a multi-select. 

      • rsbin's avatar
        rsbin
        Community Champion
        2 years ago

        ExcelMonke ,

        Then in Slicer Settings, turn On the Single select option: