A while back I was chatting with Shannon Lindsay on Twitter. She shares lots of useful Power BI tips there. She shared her syntax tip of the & operator being used for concatenation and the && operator being used for boolean AND, which reminded me about implicit conversions and blanks in DAX.
Before you read the below tweet, see how many of these you can guess correctly:
Blank + 5 = ?
Blank * 5 = ?
5 / Blank = ?
0 / Blank = ?
In DAX, Blank is converted to 0 in addition and subtraction.
What about boolean logic? Do you know the result of the following expressions?
AND(True(), Blank()) = ? OR(True(), Blank()) = ? AND(False(), Blank()) = ? AND(Blank(), Blank()) = ?
You can see the results as well as a few more permutations in the screenshot below.
Why does this matter?
You need to understand the impact of blanks in your data. Do you really want to divide by zero when you are missing data? If you are performing a boolean AND, and your data is blank, are you ok with showing a result of False? Remember that your expression may produce undesired results rather than an error.
First, you need to be aware of where it is possible in your data to get a blank input. When you are writing your DAX measures, you may need to handle blanks. DAX offers the IFERROR() function to check if the result of an expression throws an error. There is also an ISBLANK() function that you can use to check for a blank value and a COALESCE() function to provide an alternate value when a blank value is detected.
But adding extra logic in your measures may have a performance impact. For example, the DIVIDE() function can handle divide by zero errors for you. But DIVIDE() may be slower than the / operator. The performance difference is dependent on your data and the expression you are writing. Alternatively, you can use an IF statement to check if an input value is greater than zero using the > operand. This can be quicker than checking for blanks or errors using other functions.
At the end of the day, producing the correct result is more important than fast performance, but we strive to achieve both. If you have any tips for handling blanks in DAX, please share them in the comments.
Blank + 5 = 5 while Blank * 5 = Blank does not seem like “fun”. For this and other reasons I am not a fan of DAX, and in particular the DAX/Power BI combo, where a filter anywhere can affect your result when not explicitly in the formula. I apparently need to warm up to DAX, but the more I try to use it, the more I dislike it. Is there a good reference on why DAX is the way it is? So I can understand the reasons behind the decisions they made (like Blank behaving differently depending on operator, for example)? That might help. Any help appreciated.
Hi, Steve. I asked the Power BI team about why DAX handles blanks in this manner, and they sent back a quick response:
Its actually a lot more logical than SQL when you’re doing analytics.
In very naiive terms…
From an analytics standpoint, you can think of blank as an efficient 0 (call it 0’). So 0’ + x = x, 0’ * x = 0’, 0’ + 0’ = 0’.
The purpose of the ‘efficient 0’ is to speed up the compute space of what the system has to analyze, e.g.:
A crossjoin of A * B with lots of 0s in it becomes a very large table: cardinality of A * cardinality of B
But a crossjoin of A * B with lots of blanks in it becomes a very small table: cardinality of (A, B è where the value is not blank)
This makes computation of results in more complex analytics scenarios viable – without that, the dimensionality of such crossjoins makes the computation super expensive
I hope that helps some. For me, the hardest thing to understand about DAX was filter context and context transition. SQL BI has a lot of good content on this area including this video: https://www.sqlbi.com/tv/deep-dive-into-dax-evaluation-context/
So they traded decades of NULL and anything is NULL for something that makes the calculation quicker, to handle more data. I guess they had to. I don’t recall seeing that explanation in the Italians’ definitive guide to DAX. I guess since we can’t know what makes things efficient or not, we have to just follow the rules, and know ALL the rules, don’t assume that because BLANK * 5 is BLANK that BLANK + 5 is BLANK (wrong). No deriving from first principles, understanding how it works. Ugh. Evaluation context: sadistic. My fave video on this is https://youtu.be/1yWLhxYoq88, and I especially like at 37 minutes in when he explains that the value in the Total row is not the sum of the values above it. I tried to explain this to a very advanced Excel user once, and he said “how could it not?” That things are so incredibly NOT obvious in DAX/PBI seems to me to be a disservice to those of us who would like to quickly get some useful analytics out of our data. Anyway, I guess I just have to keep struggling at it. In the meantime, I do worry greatly that my results are not correct. Lots and lots of testing. Good thing the calculations are so efficient…
Forgot to say, in all my frustration, THANKS for looking into that, and the reply. The explanation did help. Still don’t like it, but it helps.
You’re quite welcome. I learned something in the process, too, so it was fun.
Two suggestions on eliminating the BLANK() issues:
1) When practical, replace null values in Power Query (M) with 0 for numeric columns and placeholder text for other columns.
2) When practical, enforce referential integrity so that all of the ‘foreign’ keys have values in the the dimension tables.
Charlie
Hi, Charlie. Nice to see you on my blog. Those are both good tips. Thanks for sharing.
Hi, In my scenario I have blanks in the date column and I can’t get rid of it in Power Query. Also I can’t replace it with any other value as it might affect the data type, please suggest how to handle it with DAX.
That sounds like a pretty specific scenario that I can’t tell you how to solve in a couple of sentences. You might want to write out your question on a forums site like https://community.powerbi.com/t5/Desktop/bd-p/power-bi-designer or Stack Overflow and see what people say. Make sure you include where and why your blanks occur and what result you are seeking with your DAX.