Forum Discussion

Deepesh_V95's avatar
Deepesh_V95
Frequent Visitor
6 years ago

Slow measure performance with Earlier function

Hi Team,

 

I have a DAX measure in my report as shown below:

 

Paid Sum_Stop Loss = CALCULATE(SUM('ops tClmsAllClmLines'[ClaimNetPay]),FILTER('ops tClmsAllClmLines',CALCULATE(SUM('ops tClmsAllClmLines'[ClaimNetPay]),FILTER('ops tClmsAllClmLines','ops tClmsAllClmLines'[MemberID] = EARLIER('ops tClmsAllClmLines'[MemberID]))) >= [SL$-MinMeasure]))
 
The above measure outputs ClaimNetPay with the mentioned filter conditions and also with a condition where result >= [SL$-MinMeasure]
 
Here [SL$-MinMeasure] is a measure which outputs Minimum value from a different another column.
 
The issue here I suppose is that the Earlier function is causing this DAX measure to render very slowly when I checked it in performance analyzer, hence hampering report speed.
 
Can you please suggest if there is a way I can use any other function or write the formula in an optimized way?
 
Thanks in advance for help!
 
Cheers,
Deepesh 
Need Help

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Deepesh_V95 

     

    Based on my research, It is the measure [SL$-MinMeasure] that causes this DAX measure to render very slowly. The measure will be calculated many times during the 'Filter' iterator. I'd like to suggest you use a variable to keep the measure out of the calculation. You may modify it as below.

     

    Paid Sum_Stop Loss = 
var x = [SL$-MinMeasure]
return
CALCULATE(
         SUM('ops tClmsAllClmLines'[ClaimNetPay]),
         FILTER(
                'ops tClmsAllClmLines',
                CALCULATE(
                          SUM('ops tClmsAllClmLines'[ClaimNetPay]),
                          FILTER(
                                'ops tClmsAllClmLines',
                                'ops tClmsAllClmLines'[MemberID] = EARLIER('ops 
                                tClmsAllClmLines'[MemberID])
                          )
                ) >= x
         )
)

     

     

     

    Best Regards

    Allan

     

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

    • Deepesh_V95's avatar
      Deepesh_V95
      Frequent Visitor
      6 years ago

      Thanks v-alq-msft . I tried this approach but still it is taking same amount of time to render. Is there anything else or any approach I can try out?

       

      Thank you!

      Deepesh

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Did you already try to make a calculated table with a calculation on 'ops tClmsAllClmLines'[MemberID]? Then replace 'ops tClmsAllClmLines'[MemberID] with the related value of that table.