cancel
Showing results for
Did you mean:

## 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:

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/

Super User

Hey,

using this DAX statement to create a calculated column

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

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
2 REPLIES 2
Super User

Hey,

using this DAX statement to create a calculated column

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

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:

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/

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors