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
Fabcon_Europe_Social_Bogo

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors