Optimizing conditions involving blank values in DAX

Learn how blank values in a DAX conditional expression can affect its query plan and how to apply possible optimizations to improve performance in these cases.
Article and download: sql.bi/483918?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin...

[@gustavobarbosa906]

Such a small detail with such large impact! Thank you!

[@dancastillo8370]

This was so helpful, thank you DAX master

[@vinayakvhegde]

😊 very useful information. Thank you for sharing this

[@2chandra]

I didn't know that Blank() and 0 are same in DAX. Thanks for valuable information.

[@SQLBI]

You can see more here: www.sqlbi.com/articles/blank-handling-in-dax/

[@kalinduSekara]

Thanks Alberto

[@pbihari0214]

Thank you

[@Anthony_Lecoq]

So great :)

[@abhinavkhanduja1856]

Hi Alberto, At approximately 6:40 you mention that the engine is doing auto-exists for the columns of the three different tables, but as far as I understand, auto-exists would kick in only if the columns are from the same table. In this instance i.e. when just using SUM(Sales[Quantity]), isn't it simply the case that SUMMARIZECOLUMNS is removing blank values from the result?

[@SQLBI]

Auto-exists is a term that we somewhat abuse of :) There is an auto-exist at the filtering level, executed by SUMMARIZECOLUMNS when multiple columns from the same table are being filtered, and another sort-of auto-exists that is an optimization in the scanning executed at the lower level, by any function. If the engine knows, from the semantics of the measure, that a non-existing combination results in a blank, then it skips that specific evaluation.
We do not have a name for this second auto-exists, therefore we call it the same way. Indeed, they are different behaviors.

[@jessamhan]

@@SQLBI Auto-blank-elimination :)

[@beginho2454]

Thanks Alberto. It is so interesting. Seems Variable version costs less time which is 68ms.
Why not choose that?

[@SQLBI]

The variable version somewhat forces eager evaluation, whereas the one without leaves a bit more freedom to the optimizer, for possible future improvements. Anyway, it is also a matter of personal taste :)

[@beginho2454]

@@SQLBI Thanks a lot sqlbi team. always sharing!

[@diegomex85]

oO amazing =)

[@michaelmays9415]

Why do you prefer the code that doesn’t use variables? If it’s because variables force eager evaluation, do you have an article of the risks?

[@SQLBI]

The variable version somewhat forces eager evaluation, whereas the one without leaves a bit more freedom to the optimizer, for possible future improvements. Anyway, it is also a matter of personal taste :)

[@sbdyelse]

I thought auto exist was only working with the column of the fact table but I was probably not understand it well yet. I Must read again about it then … hmm 🤔

[@SQLBI]

Please see the answer to Abhinav Khanduja

[@sane7263]

One Question: I am a little confused, due to Auto-Exist
will it only return the combinations which Exist in the Original data, or
will it return all the combinations but do the computation of the specified expression only on the combination which Exists in the Original data?

[@SQLBI]

Only those that exists in the original data are considering in any following computation.

[@sane7263]

@@SQLBI Got It, Thank You👍

[@sbdyelse]

I thought auto exist was only working with Column from the same table … I realize I was probably wrong. Hmm …🤔

[@SQLBI]

Please see the answer to Abhinav Khanduja