Performance problems with moving average (over a index)

[Anonymous]

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

[lbendlin]

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.

[Anonymous]

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

[lbendlin · Accepted solution]

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

Analyzing a slow report query in DAX Studio - YouTube