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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Daryl_K
Resolver II
Resolver II

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

@Daryl_K , 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 @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
Community Support
Community Support

Hi @Daryl_K ,

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

@Daryl_K , Move the blank first and try

 

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

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.