Forum Discussion

ExcelMonke's avatar
ExcelMonke
Impactful Individual
2 years ago
Solved

Singe Select Slicer with Select All

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!

Dax Help
Filter
Single Select
Slicer
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ExcelMonke ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) This is my test data. 

    (2)We can create a slicer table.

    Slicer = DATATABLE ( 
    "Column1", STRING, 
    {
        { "default" },
        { "all" }
    }
)

    (3) We can create a measure and put it to the visual filter.

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

    (4) Then the result is as follows.

    Best Regards,

    Neeko Tang

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

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    2 years ago

    hi ExcelMonke ,

     

    For a slicer, selecting no item is the same as selecting every item. 

     

    If this is not relevant, could you elaborate your expectation, ideally with dataset and corresponding expected result?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ExcelMonke ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) This is my test data. 

    (2)We can create a slicer table.

    Slicer = DATATABLE ( 
    "Column1", STRING, 
    {
        { "default" },
        { "all" }
    }
)

    (3) We can create a measure and put it to the visual filter.

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

    (4) Then the result is as follows.

    Best Regards,

    Neeko Tang

    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
      Impactful Individual
      2 years ago

      Thank you. Do you add the DATATABLE slicer as a measure or a new column?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi ExcelMonke ,

         

        It is as a slicer table. 

         

        The [column1] field of this table is then used as a slicer. Or you can create the slicer table manually by clicking enter data in the home tab.

         

        Best Regards,

        Neeko Tang

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