SWITCH function considering zeros and blanks

[Wozniak27]

I have a measure that produces zeros and blanks that I want to assign values to, such that zeros are 1 and blanks are 2.

I wrote the following but both zeros and blanks were being evaluated as 1.

SWITCH(TRUE(), measure = 0, 1, measure = BLANK(), 2)

When I adjusted the function to say the following it worked.

SWITCH(TRUE(), ISBLANK(measure), 2, measure = 0, )

Can any one explain why you have to use the ISBLANK() function in a SWITCH() function when you have both blanks and zeros?

[SteveCampbell · Accepted solution]

See explanation below, taken form https://www.sqlbi.com/articles/blank-handling-in-dax/

The behavior change only kicks in when BLANK is casted to a Boolean value in DAX. When the original column is already of Boolean data type, there is no cast, hence BLANK is preserved.

SWITCH can be rewritten as nested IF, so we only need to consider IF function.

When the two branches of IF have two different data types, IF returns variant data type. Since calculated column is always strongly typed, it raises error if the underlying DAX expression is of variant data type.

BLANK value by itself can be treated as of any (undefined) data type, so IF (<condition>, <expression>, BLANK) and IF (<condition>, BLANK, <expression>) are treated as the data type of <expression>. When <expression> is of Boolean data type, IF is of Boolean data type and an implicit cast is applied to BLANK which converts to FALSE.

BLANK here is being evaluated as FALSE which is the same as 0, so your IF function will evailatue BLANK and 0 as 0.

ISBLANK function differs slightly, and is written specifically to not cast 0s to BLANK().

So:

BLANK() = 0 = true

ISBLANK(0)= FALSE

That's why it will evaluate both as 1. Interestingly if you rewrote:

SWITCH(TRUE(), ISBLANK(measure), 2, measure = 0, 1)

as:

SWITCH(TRUE(), , measure = 0, 1,ISBLANK(measure), 2)

this would evaluate everything as 1, as it would check for 0s first, which it would cast the blank to 0.

Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!

Stay up to date on  
Read my blogs on  

Remember to spread knowledge in the community when you can! 

[SteveCampbell · Accepted solution]

See explanation below, taken form https://www.sqlbi.com/articles/blank-handling-in-dax/

The behavior change only kicks in when BLANK is casted to a Boolean value in DAX. When the original column is already of Boolean data type, there is no cast, hence BLANK is preserved.

SWITCH can be rewritten as nested IF, so we only need to consider IF function.

When the two branches of IF have two different data types, IF returns variant data type. Since calculated column is always strongly typed, it raises error if the underlying DAX expression is of variant data type.

BLANK value by itself can be treated as of any (undefined) data type, so IF (<condition>, <expression>, BLANK) and IF (<condition>, BLANK, <expression>) are treated as the data type of <expression>. When <expression> is of Boolean data type, IF is of Boolean data type and an implicit cast is applied to BLANK which converts to FALSE.

BLANK here is being evaluated as FALSE which is the same as 0, so your IF function will evailatue BLANK and 0 as 0.

ISBLANK function differs slightly, and is written specifically to not cast 0s to BLANK().

So:

BLANK() = 0 = true

ISBLANK(0)= FALSE

That's why it will evaluate both as 1. Interestingly if you rewrote:

SWITCH(TRUE(), ISBLANK(measure), 2, measure = 0, 1)

as:

SWITCH(TRUE(), , measure = 0, 1,ISBLANK(measure), 2)

this would evaluate everything as 1, as it would check for 0s first, which it would cast the blank to 0.

Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!

Stay up to date on  
Read my blogs on  

Remember to spread knowledge in the community when you can! 

[jdbuchanan71 · Accepted solution]

You can force the compare to not consider the BLANK as 0 by using the new Strict equal to operator ==

SWITCH(TRUE(),
measure == 0, 1,
ISBLANK(measure), 2)

will work the way you wanted.