Forum Discussion
Getting blanks (null) to NOT test as zero
Anonymous , Move the blank first and try
Switch( True() ,
isblank('Planning & Performance'[Defect Opportunities] ) , "Blank" ,
'Planning & Performance'[Defect Opportunities] = 0, "Zero",
"OK")
Super UserAnonymous , 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...
- viviank
Resolver I
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_AHelper II
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.
