Singe Select Slicer with Select All

[ExcelMonke]

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!

[v-tangjie-msft · Accepted solution]

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]

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

[v-tangjie-msft]

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. 

[FreemanZ]

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?