Filter column by is blank/is not blank in slicer

[leandro_vegacr]

Hello everyone.

This may be an easy one, but I'm wondering if there is a better solution to the one I implemented.

I got a table with a column which can hold any value/blank values. If the column has a value, the row represent an item of category A, if it has no value, it is of category B.

Now, the stakeholder wants to have a filter in the report (the filters pane is to be hidden) so users can decide if they want to see all items, or if they want to exclude items of category B. So, they don't want to let the users select items for category B only, it is Category A & B or Category A. Additionaly, they'd want to have the filter as "Exclude category B?" with Y/N as the options.

Now, what I did was creating a calculated column, adding a Y or N depending on the value, adding that column to a slicer, changing the name of the slicer to "Exclude category B?", then, filtered out the "N" from the slicer and made it multi select, so if "Y" is selected, the filter applies, either, everyhting is selected.

It works, but it's not explicit that deselecting the "Y" means "All" (and it should read "N" according to requirements, any way). Any idea? I tend to think this must be pretty easy and I'm not seeing something

Thanks!

[v-zhengdxu-msft · Accepted solution]

Hi @leandro_vegacr 

Here I create a sample for your reference:

Then add a new table:

Table 2 = {"A & B","A"}

Next, add a measure:

MEASURE =
VAR _newtable =
    SELECTEDVALUE ( 'Table 2'[Value] )
RETURN
    SWITCH (
        _newtable,
        "A & B",
            CALCULATE (
                SELECTEDVALUE ( 'Table'[Column1] ),
                'Table'[Column1] = "Y"
                    || 'Table'[Column1] = "N"
            ),
        "A", CALCULATE ( SELECTEDVALUE ( 'Table'[Column1] ), 'Table'[Column1] = "Y" )
    )

Add a table visual and a slicer:

The result is as follow:

Best Regards

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

[leandro_vegacr]

Hello @v-zhengdxu-msft , this is exactly what I needed, thanks so much for taking the time to add so much detail to your answer, really great!