Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Performance - Multiple measures and variables

This is not your basic "should I use a variable or measure" question. 🙂 I need to optimize variables and measures. I want to produce a measure that returns number of events this week and how that c...
Need Help
v-xicai's avatar
v-xicai
Community Support
6 years ago

Hi Anonymous ,

 

Recommend you use the Option B, since Variables are not just good for readability, they are also good for performance. About the Variable used in measure, you may refer the articles:

 

Caution When Using Variables in DAX and Power BI

 

DAX Variables: Better Readability, Consistency, and Performance in Power BI Calculations

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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?

      • marcorusso's avatar
        marcorusso
        Most Valuable Professional
        6 years ago

        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.