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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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