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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pji
Advocate I
Advocate I

BLANK() is caught as 0 in Columns

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.

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

 

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

 

PBIDesktop_2017-10-03_10-09-16.png

 

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/

 

View solution in original post

TomMartens
Super User
Super User

Hey,

 

using this DAX statement to create a calculated column

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

leads to this result

2017-10-03_17-11-34.png

 

Hopefully this is what you are looking for

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
TomMartens
Super User
Super User

Hey,

 

using this DAX statement to create a calculated column

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

leads to this result

2017-10-03_17-11-34.png

 

Hopefully this is what you are looking for

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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

 

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

 

PBIDesktop_2017-10-03_10-09-16.png

 

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/

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.