Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Getting blanks (null) to NOT test as zero

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.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Move the blank first and try

 

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

View solution in original post

6 REPLIES 6
mindileigh3
New Member

I had the same issue as @Anonymous.  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
Community Support
Community Support

Hi @Anonymous ,

In your sample, if you modify it like this:

vkalyjmsft_0-1636939839839.png

 

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.

vkalyjmsft_1-1636939839842.png

 

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
Super User
Super User

@Anonymous , Move the blank first and try

 

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

Anonymous
Not applicable

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

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.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.