Performance - Multiple measures and variables

[Anonymous]

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 changed from last week. This can get really complex since "number of events" can mean "count this type of event, add it to that type of event, and filter this third type before adding it." I'm trying to optimize performance for that process to get me to my single final output.

I'm using some of the intermediate measures elsewhere on the page. For instance, I have a card with Opened Last 7 Days so I need a measure for that anyway, but then I also need to include it in this larger calc.

So the question is: Should I create one single measure with every calculation included as variables even if it duplicates measures that already exist, or create all individual measures (which include duplicate calcs) and refer to them in the final measure? 

Option A:

Opened Last 7 Days = 
    var _MaxDate = MAX(Dates[Date])
    var _Last7Days = _MaxDate - 7
    
    var _Formula = 
        CALCULATE(
            COUNT([EventID]),
            [OpenDate] >= _Last7Days)

Closed Last 7 Days = 
    var _MaxDate = MAX(Dates[Date])
    var _Last7Days = _MaxDate - 7
    
    var _Formula = 
        CALCULATE(
            COUNT([EventID]),
            [CloseDate] >= _Last7Days)

Abandoned Last 7 Days = 
    var _MaxDate = MAX(Dates[Date])
    var _Last7Days = _MaxDate - 7
    
    var _Formula = 
        CALCULATE(
            COUNT([EventID]),
            [OpenDate] >= _Last7Days,
            [Status] = "Abandoned")

Total Changes Last 7 Days = 
    [Opened Last 7 Days] + [Closed Last 7 Days] + [Abandoned Last 7 Days]

Option B:

   var _MaxDate = MAX(Dates[Date])
   var _Last7Days = _MaxDate - 7

   var _OpenedLast7Days = 
        CALCULATE(
            COUNT([EventID]),
            [OpenDate] >= _Last7Days)

   var _ClosedLast7Days = 
        CALCULATE(
            COUNT([EventID]),
            [CloseDate] >= _Last7Days)

   var _AbandonedLast7Days = 
        CALCULATE(
            COUNT([EventID]),
            [OpenDate] >= _Last7Days,
            [Status] = "Abandoned")

Total Changes Last 7 Days = 
    _OpenedLast7Days + _ClosedLast7Days + _AbandonedLast7Days

These are WAY simplified - my actual calcs are much more complex and there are a lot more of them, which is why I need to know how and in what order PBI processes measures and variables. Is it faster/more efficient to have everything defined in one single measure, or is the performance difference close enough and I can keep them all in separate measures? Also, once PBI has calculated a measure once in one visual, does it have to re-process that for another measure or is it effectively cached? How does the processing hierarchy work - some of my intermediate measures refer to other measures, so there can be several levels it has to go through - does that impact performance?

Thank you for your help!

[v-xicai]

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]

@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?

[MFelix · Accepted solution]

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.

[Anonymous]

@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)

[marcorusso]

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.

[marcorusso]

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]

Hi @Anonymous,

This depends on what you need believe that you must make the measures and then test turn on the performance analyser to check what is the one that is getting poor results.

Has @Anonymous  you can also create some columns to make it faster to calculate but be aware that if you create columns your model and pbix file will grow in size and you may need to make some measure also depending on the final result you need. 

[Anonymous]

Hi @Anonymous 

I would create columns instead of measures for commoun cause in that case if the calculation is required by other visuals.

Once you have it in column it will be processed once and you can use it where ever you need.