Forum Discussion

BillyT_350's avatar
BillyT_350
Helper V
8 years ago
Solved

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!

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

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

13 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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"
    )
    • BillyT_350's avatar
      BillyT_350
      Helper V
      8 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        BillyT_350, 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.

    • pschn1993's avatar
      pschn1993
      Frequent Visitor
      3 years ago

      I know this is an older post, but I am having the same issue with my report. Is my below measure more complex than it needs to be? I have several measures within my report, but it seemed to slow down quite a bit after adding this one. 

       

  • jthomson's avatar
    jthomson
    Solution Sage
    8 years ago

    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

    • BillyT_350's avatar
      BillyT_350
      Helper V
      8 years ago

      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.