Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have the below formula, when adding into a table, how do i return all the Blanks (Empty spaces) as a string "N/A?
Measure =
CALCULATE(AVERAGE('Table'[Answer]),FILTER(Table,Table[Department]="Corporate Services"))
Any idea how this can be achieved, can you wrap Calculates in an IF RETURN statement perhaps? I am new to the DAX syntax so any tips would be appreciated.
Solved! Go to Solution.
Hi @aaron797 ,
You can use an IF statement indeed, and for readability I would recommend something like this:
Measure =
VAR _calculation = CALCULATE(AVERAGE('Table'[Answer]),FILTER(Table,Table[Department]="Corporate Services"))
RETURN
IF(_calculation = "", "N/A", _calculation)
You might have to tweak it a little, like replacing the <_calculation = ""> bit with "ISBLANK(_calculation)". Haven't tested the above, hopes it helps 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Try
Measure =
var _t1 = CALCULATE(AVERAGE('Table'[Answer]),FILTER(Table,Table[Department]="Corporate Services"))
return if(isblank(_t1),"N/A",_t1)
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi @aaron797 ,
You can use an IF statement indeed, and for readability I would recommend something like this:
Measure =
VAR _calculation = CALCULATE(AVERAGE('Table'[Answer]),FILTER(Table,Table[Department]="Corporate Services"))
RETURN
IF(_calculation = "", "N/A", _calculation)
You might have to tweak it a little, like replacing the <_calculation = ""> bit with "ISBLANK(_calculation)". Haven't tested the above, hopes it helps 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Try
Measure =
var _t1 = CALCULATE(AVERAGE('Table'[Answer]),FILTER(Table,Table[Department]="Corporate Services"))
return if(isblank(_t1),"N/A",_t1)
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
User | Count |
---|---|
98 | |
89 | |
82 | |
71 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |