Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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!
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
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.
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!
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |