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 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!

8 Replies

  • 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's avatar
    Anonymous
    Not applicable

    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.

     

     

  • v-xicai's avatar
    v-xicai
    Community Support

    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

      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?