cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors