cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jengwt Resolver I
Resolver I

Slow Measures

I have report that analyzes data on customers. Data is filtered by three slicers, one for customer, another for data date, and the third for internal business unit (responsible for the customer).

 

Certain calculations are then made (via measures), and finally a letter grade is assigned based on the how those calculations score.

 

All of the measures in this report function quickly, within a matter of seconds, except the letter grade ones, which usually take minutes. Why is this? They are easily the simplest measures in the report.

Here is a sample of their code:

 

Grd_ApptDesir = IF([Scr%_ApptDesir]>=.9
          , "A"
          , IF(AND([Scr%_ApptDesir]<.9,[Scr%_ApptDesir]>=.8)
               , "B"
               , IF(AND([Scr%_ApptDesir]<.8,[Scr%_ApptDesir]>=.7)
                    , "C"
                    , IF(AND([Scr%_ApptDesir]<.7,[Scr%_ApptDesir]>=.6)
                         , "D"
                         , "F"
                         )
                    )
               )
          )
     )

 

Any ideas why this is occuring? How to make it better?

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHaas Solution Sage
Solution Sage

Re: Slow Measures

With this measure, you are calling, and calculating the [Scr%_ApptDesir] measure 7 times...it only needs to be calculated once.

 

try this:

 

Grd_ApptDesir =
VAR Score = [Scr%_ApptDesir]
RETURN
    SWITCH ( TRUE (),
        Score >= .9, "A",
        Score >= .8, "B",
        Score >= .7, "C",
        Score >= .6, "D",
        "F"
    )

View solution in original post

12 REPLIES 12
jthomson Solution Sage
Solution Sage

Re: Slow Measures

I'm not sure why you're using an AND function in every bit of your measure, the way it's coded the first requirement is completely superfluous (e.g. in the second stage, it must be less than 0.9, as if it was 0.9 or more it's not reaching the second IF in the first place), don't know whether that'd make a huge difference in performance though

jengwt Resolver I
Resolver I

Re: Slow Measures

@jthomson That's true, I just prefer to have everything defined for the freak occurences / Power BI glitches.

 

Nonetheless, as per your suggestion, I took out the redundant ANDs, and there is no perceptable change.

ChrisHaas Solution Sage
Solution Sage

Re: Slow Measures

With this measure, you are calling, and calculating the [Scr%_ApptDesir] measure 7 times...it only needs to be calculated once.

 

try this:

 

Grd_ApptDesir =
VAR Score = [Scr%_ApptDesir]
RETURN
    SWITCH ( TRUE (),
        Score >= .9, "A",
        Score >= .8, "B",
        Score >= .7, "C",
        Score >= .6, "D",
        "F"
    )

View solution in original post

jengwt Resolver I
Resolver I

Re: Slow Measures

@ChrisHaas Thank you! I had considered using switch statements, but I wasn't sure how to use them. Thank you for a good example!

 

I modified those measures with switch statements, and load times for those grade measures took over 60% less time! I'd call that a solution.

 

I had suspected that Power BI recalculates measures when you call them, but not multiple times in the same measure! That seems buggy...

Also, those Scr% measures are based off of several others, so I just attributed the slow lead times to those "nested" calls. I have a thread that got lost somewhere about "3rd Tier Measures".

 

Thanks for your help!

ChrisHaas Solution Sage
Solution Sage

Re: Slow Measures

@jengwt, no worries!

 

I realize that it seems buggy, but consider this:

 

Everytime the measure is called in an expression, the filter and/or context may be different.  That is why it needs to be evaluated separately each time.

 

Paste the other measures that are referenced in the final measure, we may be able to optimize those as well.

Highlighted
jengwt Resolver I
Resolver I

Re: Slow Measures

@ChrisHaas That's true, but PBI already calculates a given measure any time you change a slicer. So why isn't a given measure just stored as a variable on the back end? It only needs to change if the user changes it's filtering, and any number of other measures, columns, etc, should be able to call it at will.

 

I'm already at work looking to add in switch statements to speed things up in other places in this report and other reports that I've made. What other types of recursive statements are there? A "switch" is basically a "case". What about "for/foreach"s?

 

Here is a sample of one of the Scr%s. Some of what I do here is necesary because of the way the data is structured.

[m_ApptTimes] is simply a sum(x)/sum(y)

I used [m_ApptTimes] > AVERAGE...  because I have to use a function in a CALCULATE, and the average of one item is simply itself. However, if for some weird reason there are multiple values, using average should lessen the damage, vs a sum, product, etc.

In short, the Scr% codes check to see if the a metric falls within a certain range. If it it outside the range, it will produce a 100% or 0%. Otherwise, the percentage is calculated  based on the range.

 

Now, these still load quite quickly. The worse offender by far is still the letter grade measures above.

 

Scr%_ApptDesir = IF(CALCULATE([m_ApptTimes] < AVERAGE('CUS_RANGE'[MIN_VALUE])
     , FILTER('CUS_RANGE'
          , AND('CUS_RANGE'[BUS_UNIT] = SELECTEDVALUE('MTHLY'[IBU], "")
               , AND('CUS_RANGE'[SUMMARY_TYPE] = "ACCT_NBR"
                    , 'CUS_RANGE'[METRIC_NAME] = "APPT_DESIR"
                    )
               )
         )
    )
    , 1
         , IF(CALCULATE([m_ApptTimes] > AVERAGE('CUS_RANGE'[MAX_VALUE])
               , FILTER('CUS_RANGE'
                    , AND('CUS_RANGE'[BUS_UNIT] = SELECTEDVALUE('MTHLY'[IBU], "")
                         , AND('CUS_RANGE'[SUMMARY_TYPE] = "ACCT_NBR"
                                , 'CUS_RANGE'[METRIC_NAME] = "APPT_DESIR"
                                )
                         )
                   )
             )
     , 0
     , CALCULATE( (1 - ( ([m_ApptTimes] - AVERAGE('CUS_RANGE'[MIN_VALUE])) / (AVERAGE('CUS_RANGE'[MAX_VALUE]) - AVERAGE('CUS_RANGE'[MIN_VALUE])) ))
          , FILTER('CUS_RANGE'
               , AND('CUS_RANGE'[BUS_UNIT] = SELECTEDVALUE('MTHLY'[IBU], "")
                     , AND('CUS_RANGE'[SUMMARY_TYPE] = "ACCT_NBR"
                          , 'CUS_RANGE'[METRIC_NAME] = "APPT_DESIR"
                          )
                     )
                )
           )
      )
  )
ChrisHaas Solution Sage
Solution Sage

Re: Slow Measures

@jengwt, this will optimize your measure a little bit:

 

Scr%_ApptDesir =
VAR FilteredTable =
    FILTER (
        'CUS_RANGE',
        'CUS_RANGE'[BUS_UNIT] = SELECTEDVALUE ( 'MTHLY'[IBU], "" )
            && 'CUS_RANGE'[SUMMARY_TYPE] = "ACCT_NBR"
            && 'CUS_RANGE'[METRIC_NAME] = "APPT_DESIR"
    )
RETURN
    IF (
        CALCULATE ( [m_ApptTimes] < AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ), FilteredTable ),
        1,
        IF (
            CALCULATE ( [m_ApptTimes] > AVERAGE ( 'CUS_RANGE'[MAX_VALUE] ), FilteredTable ),
            0,
            CALCULATE (
                (
                    1
                        - DIVIDE (
                            ( [m_ApptTimes] - AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ) ),
                            ( AVERAGE ( 'CUS_RANGE'[MAX_VALUE] ) - AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ) )
                        )
                ),
                FilteredTable
            )
        )
    )

You're using the same filter arugment in 3 different instances, so there's no need to scan the table 3 times to make the same table.  Just assign it to a variable and off you go.

 

I'm also a little curious about this expression.  is it a calculated column, or a measure?  If it's a measure, I would do this to further optimize:

 

Scr%_ApptDesir =
VAR FilteredTable =
    FILTER (
        'CUS_RANGE',
        'CUS_RANGE'[BUS_UNIT] = SELECTEDVALUE ( 'MTHLY'[IBU], "" )
            && 'CUS_RANGE'[SUMMARY_TYPE] = "ACCT_NBR"
            && 'CUS_RANGE'[METRIC_NAME] = "APPT_DESIR"
    )
VAR AverageMin =
    AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ) 
/*Or this
	CALCULATE(
		AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ), 
		FilteredTable
	)
*/
VAR AverageMax =
    AVERAGE ( 'CUS_RANGE'[MAX_VALUE] ) 
/*Or this
	CALCULATE(
		AVERAGE ( 'CUS_RANGE'[MAX_VALUE] ), 
		FilteredTable
	)
*/
RETURN
    IF (
        CALCULATE ( [m_ApptTimes] < AverageMin, FilteredTable ),
        1,
        IF (
            CALCULATE ( [m_ApptTimes] > AverageMax, FilteredTable ),
            0,
            CALCULATE (
                (
                    1
                        - DIVIDE ( ( [m_ApptTimes] - AverageMin ), ( AverageMax - AverageMin ) )
                ),
                FilteredTable
            )
        )
    )

I can't tell if you want the TRUE/FALSE statement to be in the calculate expression, or if you want to compare the [m_ApptTimes] measure to the Average of the Min/Max column, and have both of them respect the filtered table variable.

 

Even still, the first expression will be at least a little faster.

jengwt Resolver I
Resolver I

Re: Slow Measures

@ChrisHaas the Scr%s are measures. A few tweaks, and I think this will work well.

 

To answer your question, we're merely checking to see if the calculated appointment times [m_ApptTimes] are greater than the min or the max allotted (contained with many others in table 'CUS_RANGE'). So, the two ways you've coded it would need to be altered a little to be like this:

RETURN
    IF (
        [m_ApptTimes] < CALCULATE (  AVERAGE('CUS_RANGE'[MIN_VALUE]), FilteredTable ),
        1, ...

or, 

VAR AverageMin =
    CALCULATE(
		AVERAGE ( 'CUS_RANGE'[MIN_VALUE] ), 
		FilteredTable
	)

VAR AverageMax =
    CALCULATE(
		AVERAGE ( 'CUS_RANGE'[MAX_VALUE] ), 
		FilteredTable
	)

RETURN
    IF (
        [m_ApptTimes] < AverageMin,
        1,
        IF (
            [m_ApptTimes] > AverageMax,
            0, ...

So, it would depend on which of the two methods used. Would it be faster to forgoe creating those variables, as each is only called twice anyways?


Thanks again! You've really advanced my knowledge of DAX. I didn't know that DAX could do half of the things that you've done in this code. I will go back and review more of my code, and see where I can use your suggestions.

I think the the VARs will be particularly useful.

 

jengwt Resolver I
Resolver I

Re: Slow Measures

@ChrisHaas Implemented those changes. Page refreshes down to 10 seconds! This is what I've got them looking like now:

 

Scr%_ApptWinHrs = 

VAR FilterTable = FILTER('CUS_RANGE'
     , 'CUS_RANGE'[BUS_UNIT] = SELECTEDVALUE('MTHLY'[IBU], "")
          && 'CUS_RANGE'[SUMMARY_TYPE] = "ACCT_NBR"
          && 'CUS_RANGE'[METRIC_NAME] = "APPT_WIN_HR")

VAR MinVal = CALCULATE(AVERAGE('CUS_RANGE'[MIN_VALUE]), FilterTable)

VAR MaxVal = CALCULATE(AVERAGE('CUS_RANGE'[MAX_VALUE]), FilterTable)

VAR Metric = [m_ApptWinHrs]

RETURN
     SWITCH( TRUE()
          , Metric > MinVal, 1
          , Metric < MaxVal, 0
          , (Metric - MaxVal) / (MinVal - MaxVal)
          )

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors