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

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

rsbin's avatar
rsbin
Icon for Community Champion rankCommunity Champion
2 years ago

ExcelMonke ,

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

 

  • 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"

     

    • rsbin's avatar
      rsbin
      Icon for Community Champion rankCommunity Champion
      2 years ago

      ExcelMonke ,

      Am really not understanding.  Based on your last explanation, that is why I suggested to Turn On the Select All option in your Slicer visual in my original reply. 

      Your User, with one click, can Select all, or select one of the two options individually.  That is exactly what it is designed for.

      The other option available to you is to use Bookmarks, Buttons and the Filter Pane.  But in my opinion, that seems to going to an extreme length to solve this issue.

      Kind Regards,

    • 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.