SOLVED

IF statement for value 0

Copper Contributor

I need to be able to differentiate 0 vs blank values in a DAX formula. 

I've tried using ==0 for the number 0 and ="" for blanks, but == seems so be giving error in my DAX formula. How do I make this work? Attached a simplified dummy file, but with the same problem scenario. 

 

DAX that gives no errors:

=IF(C5=0, "order more", IF(C5 ="", "unavailable", "OK"))

What I would like to do but can't get to work:
=IF(C5==0, "order more", IF(C5 ="", "unavailable", "OK"))

3 Replies
best response confirmed by Wiiilda (Copper Contributor)
Solution

@Wiiilda 

"==" is not supported in Excel. How to handle blanks is here Handling BLANK in DAX - SQLBI

Back to your case. BLANK()=0 returns TRUE, but ISBLANK(0) returns FALSE. Thus formula could be

image.png

Please check in attached file.

Thank you. Is there some similar solution if I want to exclude value 0 but keep blanks with an advanced filter.

I've tried amount: <>"0"

@Wiiilda 

That will be

="<>0"

in criteria

1 best response

Accepted Solutions
best response confirmed by Wiiilda (Copper Contributor)
Solution

@Wiiilda 

"==" is not supported in Excel. How to handle blanks is here Handling BLANK in DAX - SQLBI

Back to your case. BLANK()=0 returns TRUE, but ISBLANK(0) returns FALSE. Thus formula could be

image.png

Please check in attached file.

View solution in original post