Forum Discussion

Gabriel_Walkman's avatar
Gabriel_Walkman
Icon for Continued Contributor rankContinued Contributor
5 years ago
Solved

Separate filter checkbox for blanks

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

Need Help
  • Icey's avatar
    Icey
    5 years ago

    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.

5 Replies

    • Gabriel_Walkman's avatar
      Gabriel_Walkman
      Icon for Continued Contributor rankContinued Contributor
      5 years ago

      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.

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
      Gabriel_Walkman
      Icon for Continued Contributor rankContinued Contributor
      5 years ago

      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