locked
DAX - Return zero if blank RRS feed

  • Question

  • 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. 

    Thursday, February 4, 2016 7:05 PM

Answers

  • 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.
    • Marked as answer by VivDev Friday, February 5, 2016 1:02 PM
    Friday, February 5, 2016 7:28 AM
    Answerer

All replies

  • 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.
    • Marked as answer by VivDev Friday, February 5, 2016 1:02 PM
    Friday, February 5, 2016 7:28 AM
    Answerer
  • Wow.. Simple and brilliant! Thanks. 
    Friday, February 5, 2016 1:02 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.
    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.
    @BL_Quest Any idea how one can deal with this problematic outcome? You described exactly my case!
    Thursday, June 28, 2018 6:23 PM
  • Set the Format to Custom and the format string to something like this:

    #,0;(#,0)

    Wednesday, July 25, 2018 1:28 AM
  • I got the same issue - does anyone have any idea on resolving this ?
    Sunday, June 16, 2019 8:41 PM