cancel
Showing results for
Did you mean:
New Member

## How to do a double For/While?

I have a table with the following information:

 Index Iteration Points 1 38 19 2 39 16 3 40 13 4 41 15 5 42 16 6 43 16

I need a double while/for to create the average of the last 3 iterations to a new table: (Example)

 Index Iteration Average 1 40 = (19+16+13) / 3 2 41 = (16+13+15) / 3 3 42 = (13+15+16) / 3 4 43 = (15+16+16) / 3

I think recursion is not possible in Dax Code but I've tried different things to see a workaround.

1 ACCEPTED SOLUTION
Community Support

Hi @donatoath,

If you would like to achieve the desired result with a DAX formula, you may try this Measure.

``````Avg =
VAR avg_ =
CALCULATE (
AVERAGE ( 'Table'[Points] ),
FILTER (
ALL ( 'Table' ),
'Table'[Iteration] >= MAX ( 'Table'[Iteration] )
&& 'Table'[Iteration]
< ( MAX ( 'Table'[Iteration] ) + 3 )
)
)
VAR maxIndex =
CALCULATE ( MAX ( 'Table'[Index] ), ALL ( 'Table' ) )
RETURN
IF ( MAX ( 'Table'[Index] ) > maxIndex - 2, BLANK (), avg_ )``````

Then, the result looks like this.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

2 REPLIES 2
Community Support

Hi @donatoath,

If you would like to achieve the desired result with a DAX formula, you may try this Measure.

``````Avg =
VAR avg_ =
CALCULATE (
AVERAGE ( 'Table'[Points] ),
FILTER (
ALL ( 'Table' ),
'Table'[Iteration] >= MAX ( 'Table'[Iteration] )
&& 'Table'[Iteration]
< ( MAX ( 'Table'[Iteration] ) + 3 )
)
)
VAR maxIndex =
CALCULATE ( MAX ( 'Table'[Index] ), ALL ( 'Table' ) )
RETURN
IF ( MAX ( 'Table'[Index] ) > maxIndex - 2, BLANK (), avg_ )``````

Then, the result looks like this.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

Super User

A new column

Var _sum = sumx(filter(Table, [Index]>= earlier([Index]) && [Index]<= earlier([Index]) +2) , [Points] )

Var _cnt = countx(filter(Table, [Index]>= earlier([Index]) && [Index]<= earlier([Index]) +2) , [Points] )

return

divide(_sum,_cnt)

If needed you can check for _cnt > 2

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Announcements