Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Always select Blank in the Filter

Hello 😄

I want to improve the User Experience of my report, so i was wondering if there's any way to deal with this kind of situation:

I have a left joined table, a column call 'seasons' for example, has ['Blank','Summer','Winter','Fall','Spring'].

 

I need that always that an user select a season (Summer','Winter','Fall','Spring) also includes the 'Blank' ones.

I know that is solve by just selecting both options but i want that the users dont know that, if they select Summer & Winter for example the 'Blank' option will be also selected automatically somehow.

 

Thank you!!

 

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Anonymous ,

    Please try like this:

    • Create a table
    Seasons = VALUES('Table'[Seasons])
    • Create a measure
    Measure =
IF (
    SELECTEDVALUE ( 'Table'[Seasons] ) IN ALLSELECTED ( Seasons[Seasons] )
        || SELECTEDVALUE ( 'Table'[Seasons] ) = BLANK (),
    1,
    0
)

     

    For more details, please see the attachment.

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago
    You would have to write your measures to account for this. The general structure would be something like:

    Measure =
    VAR __Selected = SELECTEDVALUE('Table'[Season])
    VAR __Table = FILTER(ALL('Table'),[Season] = __Selected || ISBLANK([Season]))
    RETURN
    <some calculation over __Table using "X" functions>
  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    Please try like this:

    • Create a table
    Seasons = VALUES('Table'[Seasons])
    • Create a measure
    Measure =
IF (
    SELECTEDVALUE ( 'Table'[Seasons] ) IN ALLSELECTED ( Seasons[Seasons] )
        || SELECTEDVALUE ( 'Table'[Seasons] ) = BLANK (),
    1,
    0
)

     

    For more details, please see the attachment.

     

    • ms_flxx's avatar
      ms_flxx
      New Member
      2 years ago

      Hi, I would like to build on your answer and ask if it is possible to modify the procedure so that cross-filtering is possible. I have already opened a new issue for this: Always select Blank in the Filter combined with Cross Filtering 

       

      Basically, I want to filter a table using two slicers that include all blanks for a selected option. At the same time, however, I want to ensure that slicer 2 only displays the options that remain after the table has been filtered by the first filter. Do you have any ideas?