Getting blanks (null) to NOT test as zero

[Daryl_K]

So it seems every search on this topic I am coming up with questions on making null values (blank cells) show as zero, but what I am looking for is exactly the opposite. But first, let me clarify: the blank cells are NOT populating with zeros. I have a columns where some rows have valid values, some have zero, and some are blanks. This is exactly the way I want it.

The problem is that I am making a compliance page based on this info and am trying to use nested IF statements to test if it is blank, is populated with zero or some other value (as shown below). Note that the column  Defect Opportunities is formatted as whole number.

IF('Planning & Performance'[Defect Opportunities] = 0, "Zero", IF('Planning & Performance'[Defect Opportunities] = BLANK() , "Blank", "OK")) 

But the Blank() condition never tests true. Both blank cells and those populated with zero get the "Zero" response from this statement.

Should I format as text? Is there something else I am missing?

P.S. Forgt to mention that I aslo tried ISBLANK, but the result was identical.

[mindileigh3]

I had the same issue as @Daryl_K.  I could not, however, resolve it with changing blank() to "" because I do not own the original measures.  I was able to resolve it with using "if(not(isblank([measure_name])), ...)" instead of "if(isblank([measure_name]), ....".  Strange.

More info here: https://community.powerbi.com/t5/Desktop/DAX-measure-IF-a-value-is-not-blank/m-p/677117 

[v-yanjiang-msft]

Hi @Daryl_K ，

In your sample, if you modify it like this:

You can see it also return the wrong result, because DAX treats BLANK() and 0 (zero) as the same value, You can use the ISBLANK function to distinguish blank and 0.

Here’s some information may help you to understand.

https://www.burningsuit.co.uk/how-dax-treats-nulls-zeros/

https://docs.microsoft.com/en-us/dax/isblank-function-dax

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Daryl_K , Move the blank first and try

Switch( True() ,
isblank('Planning & Performance'[Defect Opportunities] ) , "Blank" ,
'Planning & Performance'[Defect Opportunities] = 0, "Zero",
"OK")

[Daryl_K]

Well...it sort of reversed the issue. Now both blank cells and those with a "0" read as "Blank."

Hmm, based on that result it seems DAX is unable to distinguish between a null and a zero. Is what I am trying to do even possible?

EDIT: I finally had an opportunity to change format of the source column, then of course twiddle the DAX to align with string data (changed BLANK() to "" and 0 to "0") and now it works fine. All three conditions return correctly.

I am far from an Power BI expert and wish I knew exactly why DAX makes no differentiation between null and 0 in cells formatted to whole numbers. I guess I'll have to re-format the data in another location...

[viviank]

https://docs.microsoft.com/en-us/dax/blank-function-dax

Blanks and empty strings ("") are not always equivalent, but some operations may treat them as such.

Looks like your data has empty strings, which are different than a BLANK.

[ERIC_A]

hi the switch formula worked fine but for me, the trick was to add the ISBLANK case first than any others and then this worked. If I had the 0 case first, it didn't work as expected.