Forum Discussion
Performance - Multiple measures and variables
Hi Anonymous ,
Check this post from SQL BI.
https://www.sqlbi.com/articles/optimizing-dax-expressions-involving-multiple-measures/
This is an article from marcorusso and he can help to clarify some of these more complex issues around if we should use variables, measures or columns.
marcorusso can you enlight us all with the information requested.
Thank you.
v-xicai Thanks Amy.
I read those and a bunch of other articles, but I couldn't find a definitive answer. I know that PBI processes a column once, but does it only process a measure once?
If it processes a measure the first time it encounters it and then caches it to use in other calcs on the page that use that same measure, then it should be much faster to use measures since I reuse them in a bunch of different places. If it processes the measure from scratch every time it encounters it, then there should be no advantage (in this case) to using measures over variables, and I should write out the calc as a variable every time I want to use it. Further, if there's a processing lag to run down the hierarchy of measures (measure C refers to measure B, which refers to measure A), then I should recreate the hierarchy as variables each time I need it.
Any ideas on how PBI processes those?
Hi Anonymous ,
Check this post from SQL BI.
https://www.sqlbi.com/articles/optimizing-dax-expressions-involving-multiple-measures/
This is an article from marcorusso and he can help to clarify some of these more complex issues around if we should use variables, measures or columns.
marcorusso can you enlight us all with the information requested.
Thank you.
A measure is just a way to represent a CALCULATE expression, the engine always replaces measures with the corresponding CALCULATE that is evaluated in the proper filter context. The cache works only at the storage engine level.
There are several chapters in The Definitive Guide to DAX to explain how this works, which is part of the explanation required to learn how to optimize DAX expressions.
MFelix Thank you! I searched for this on SQLBI and somehow didn't come up with it. Much appreciated.
marcorusso So if I understand what you said here and in the article, multiple measures will probably result in multiple calls, so condensing via variables into one measure is probably better? (Unfortunately I'm on a work computer and can't install DAXQuery, so all I have is the Performance Analyzer)
As I said, is more complex than that. Variables could be useful to avoid multiple calculations within the same DAX expressions, but other than that it really depends on the overall query execution plan.
