The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
@Greg_Deckler I found a solution that works
@mariahMC Try: AvgScoreBlank = IF([Average Score]=BLANK(),"-",[Average Score] & "")
@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.
@Greg_Deckler I found a solution that works
@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.