cancel
Showing results for 
Search instead for 
Did you mean: 
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
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.