Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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!!

 

1 ACCEPTED SOLUTION
v-xuding-msft
Community Support
Community Support

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
)

 2.gif

For more details, please see the attachment.

 

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

View solution in original post

3 REPLIES 3
v-xuding-msft
Community Support
Community Support

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
)

 2.gif

For more details, please see the attachment.

 

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

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?

Greg_Deckler
Super User
Super User

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>

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.