DAX - Return zero if blank
Question
Thursday, February 4, 2016 7:05 PM
I want to return a zero if a formula returns a blank
e.g. Won:=CALCULATE([TCV], Sales[Stage]="Won") will return blank if there are no records with Stage as Won.
I could write something like this but want to know if there is a better/simpler way.
Won:=IF(ISBLANK(CALCULATE([TCV], Sales[Stage]="Won")), 0, CALCULATE([TCV], Sales[Stage]="Won"))
I have some fairly complex/long formulas and repeating the formula once to check if it returns a blank and then again repeat it to return the value seems pretty inefficient and makes the formulas even longer.
Ideally, an IFBLANK like IFERROR existed that would have been perfect.
All replies (6)
Friday, February 5, 2016 7:28 AM âś…Answered | 1 vote
Adding +0 at the end of your DAX formula will force Power Pivot to convert the value to a number, and blanks will become zeros.
Friday, February 5, 2016 1:02 PM
Wow.. Simple and brilliant! Thanks.
Monday, February 12, 2018 4:04 PM
Be careful with +0, it will force showing of 0s against dimensions even when there is no data i.e. You have 0 sales in 2015,2016 but the measure will still show 0 and FORCE showing of 2015 & 2016, this can be mistaken as Sales were 0 even though there was no data for those years and should not have shown up in the first place.
Thursday, June 28, 2018 6:23 PM
Be careful with +0, it will force showing of 0s against dimensions even when there is no data i.e. You have 0 sales in 2015,2016 but the measure will still show 0 and FORCE showing of 2015 & 2016, this can be mistaken as Sales were 0 even though there was no data for those years and should not have shown up in the first place.
@BL_Quest Any idea how one can deal with this problematic outcome? You described exactly my case!
Wednesday, July 25, 2018 1:28 AM
Set the Format to Custom and the format string to something like this:
#,0;(#,0)
Sunday, June 16, 2019 8:41 PM
I got the same issue - does anyone have any idea on resolving this ?