Separate filter checkbox for blanks

[Gabriel_Walkman]

Hi yall,

I have a list of sales teams and a bunch of data connected to them. Not all data can be connected to a seller or a team though, as either the seller is missing from the teams list, or the data has missing values.

Now for my question: can I somehow have two elements: a dropdown filter for the teams without the option for blanks, and a separate checkbox called something like "Include blank teams"?

For the moment I just have one dropdown for the teams, and you can de/select blanks from that. But I think having the option for blanks as a separate element would be clearer for the user.

Thanks in advance,
Gabe

[Icey · Accepted solution]

Hi @Gabriel_Walkman ,

Please check if this could meet your requirements:

Measure 1 = 
IF (
    SELECTEDVALUE ( 'Include Blank or not'[Include Blank or Not], "Yes" ) = "Yes",
    1,
    IF (
        SELECTEDVALUE ( 'Include Blank or not'[Include Blank or Not] ) = "No"
            && MAX ( Teams[Team] ) <> BLANK (),
        1
    )
)

Measure 2 = 
IF (
    SELECTEDVALUE ( 'Include Blank or not'[Include Blank or Not], "Yes" ) = "Yes",
    1,
    IF (
        SELECTEDVALUE ( 'Include Blank or not'[Include Blank or Not] ) = "No"
            && MAX ( Sales[Team] ) IN DISTINCT ( Teams[Team] ),
        1
    )
)

Best Regards,

Icey

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

[Gabriel_Walkman]

In the end, my teams table setup was quite different: value table has seller id, teams table has seller id and corresponding team name, thus I can get blank teams either by value missing a seller id, or teams table missing the seller id.

With your help I managed to do a calculation item with tabular editor, which seems to work nicely on my simple test.

Calculation item:

if( 
    SELECTEDVALUE ( 'Include Blanks'[option] ) = "Include Blanks",
    selectedmeasure(),
    calculate(
        selectedmeasure(),
        filter(
            'teams',
            max( 'teams'[team] ) <> BLANK()
        )
    )
)

 
pbix

[Gabriel_Walkman]

Huge thanks for the awesome representation of your answer!

[amitchandak]

@Gabriel_Walkman , Option like "Include blank teams", needs to do with help from an independent table.

It is good option to have.

Example video: https://www.youtube.com/watch?v=lOEW-YUrAbE

[Gabriel_Walkman]

Thank you for the suggestion. I tried fooling around with the methods in the end of the video, but they don't seem to respond well to blanks.