Forum Discussion
Helper IReplace 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(NOT(ISBLANK([Average Score])),SWITCH(TRUE(), [Average Score]=0, "-",[Average Score]))
//Puts "-" in zero values but not in blank values
Greg_Deckler I found a solution that works
AvgScoreBlank =IF( NOT ISEMPTY('Reviews'),COALESCE([Average Score],"-"))
5 Replies
- Greg_Deckler
Community Champion
mariahMC Try: AvgScoreBlank = IF([Average Score]=BLANK(),"-",[Average Score] & "")
- mariahMC
- Greg_Deckler
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.
