BLANK() is caught as 0 in Columns

[pji]

How to differentiate between 0 and blank in DAX queries? Whatever query I write, it always considers 0 the same as blank().

My data has blank and 0 as two different values instead of being the same thing.

[Anonymous · Accepted solution]

Try using ISBLANK() instead of comparing to BLANK().

For some reason, those are treated differently. Check out this screenshot:

The two DAX formulas:

BlankCheck = ISBLANK(Numbers[Number])

BlankCheckIf = IF(Numbers[Number] = BLANK(), TRUE(), FALSE())

Here's a good article for some additional information, including truth tables with BLANK() comparisons:

https://www.sqlbi.com/articles/blank-handling-in-dax/

[TomMartens · Accepted solution]

Hey,

using this DAX statement to create a calculated column

Column = 
IF(ISBLANK('ColumnWithMissingValues'[ColumnWithMissingValues]), "TRUE", "FALSE")

leads to this result

Hopefully this is what you are looking for

Regards

Tom

[Anonymous · Accepted solution]

Try using ISBLANK() instead of comparing to BLANK().

For some reason, those are treated differently. Check out this screenshot:

The two DAX formulas:

BlankCheck = ISBLANK(Numbers[Number])

BlankCheckIf = IF(Numbers[Number] = BLANK(), TRUE(), FALSE())

Here's a good article for some additional information, including truth tables with BLANK() comparisons:

https://www.sqlbi.com/articles/blank-handling-in-dax/