topic Performance problems with moving average (over a index) in Developer

Performance problems with moving average (over a index)

Anonymous — Thu, 16 Jun 2022 13:08:41 GMT

Hello, I have a problem with the calculation time when determining a moving average( not via date values, but via an index). The table is loaded into Power BI.
The table has about 100,000 rows and about 80 columns.
I calculate a moving average (calculated column) using:

Moving Average =

VAR MyIndex = RRK_A[Index]

VAR Window_A = 299

VAR myResult =

SUMX(

FILTER(

RRK_A,

RRK_A[Index] > MyIndex-Window_A &&

RRK_A[Index] <= MyIndex

),RRK_A[RRK_A_FLAG]

)

RETURN FIXED(myResult,2)

Further I use a MEASUR:

Moving1 =

VAR currentIndex = MAX('RRK A'[Index])

VAR Grenze = 10

VAR movingAverage = CALCULATE(SUM('RRK A'[RRK_A_flag]), FILTER(ALLSELECTED('RRK A'), 'RRK A'[Index] > currentIndex -

Grenze && 'RRK A'[Index] <= currentIndex))

return movingAverage

Does anyone have an idea how I can improve the performance?
Thanks for help

Re: Performance problems with moving average (over a index)

lbendlin — Sat, 18 Jun 2022 01:51:54 GMT

Use DAX Studio to study the query plan generated by your measures. Change your measures to reduce the number of records/iterations in the query plans.

Re: Performance problems with moving average (over a index)

Anonymous — Sat, 18 Jun 2022 16:56:56 GMT

Thank you for your advice lbendlin.
But unfortunately I don't know what you mean by that?
Can you perhaps give me a hint?

Thanks Friedbert

Re: Performance problems with moving average (over a index)

lbendlin — Sun, 19 Jun 2022 00:15:15 GMT

DAX Studio - The ultimate client tool for working with DAX queries

Analyzing a slow report query in DAX Studio - YouTube