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.
Solved! Go to Solution.
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.
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.
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.
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...
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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.