Slow measure performance with Earlier function

[Deepesh_V95]

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 

[Anonymous]

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.

[v-alq-msft]

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]

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