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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
Community Champion
Community Champion

@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!:
DAX For Humans

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!:
DAX For Humans

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
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors