Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
83 | |
75 | |
64 | |
39 | |
34 |
User | Count |
---|---|
107 | |
56 | |
52 | |
48 | |
40 |