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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
mariahMC
Helper I
Helper I

Replace blanks with a dash without ignoring filters

I have an average measure that averages answers to questions (yes = 1, no =0). My matrix visual shows averages for each question over 4 Quarters and is filtered by procedure type. Some quarters may have blank values for certain questions and instead of showing blank I want to show a dash "-". However, when I try to use an IF statement, it ignores my filters and shows all the questions regardless of the procedure type selected.

 

My average measure:

Average Score = AVERAGE('Reviews'[Binary])

 

Here are my attempts at replacing the blanks with a dash:

AvgScoreBlank = IF(ISBLANK([Average Score]),"-",[Average Score])

//Puts "-" in blank values, but ignores filter

 

AvgScoreBlank = IF([Average Score]=BLANK(),"-",[Average Score])
//Puts "-" in blank values, but ignores filter
 

AvgScoreBlank = IF(NOT(ISBLANK([Average Score])),SWITCH(TRUE(), [Average Score]=0, "-",[Average Score]))

//Puts "-" in zero values but not in blank values

 

mariahMC_0-1750442417635.png

 

 

 

1 ACCEPTED SOLUTION

@Greg_Deckler I found a solution that works

AvgScoreBlank =
IF( NOT ISEMPTY('Reviews'),COALESCE([Average Score],"-"))

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

@mariahMC Try: AvgScoreBlank = IF([Average Score]=BLANK(),"-",[Average Score] & "")



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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler unfortunately, that didn't work and it turned the average values into text 

mariahMC_0-1750450291772.png

 

@mariahMC Yeah, that's kind of the point, DAX doesn't like measures returning variant data types like sometimes numbers and sometimes text. You could do this:

Try: AvgScoreBlank = IF([Average Score]=BLANK(),"-",FORMAT( [Average Score], "Percent"))

 

You could also try measure conditional formatting but I believe that's preview.



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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler I found a solution that works

AvgScoreBlank =
IF( NOT ISEMPTY('Reviews'),COALESCE([Average Score],"-"))

@Greg_Deckler The measure AvgScoreBlank = IF([Average Score]=BLANK(),"-",[Average Score]) works as intended, the issue is that it's ignoring my slicer filter. When I select a procedure type from the slicer, it should only show the questions and averages relevant to that procedure, however the AvgScoreBlank measure is showing all questions with blank values. 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.