Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
donatoath
New Member

How to do a double For/While?

I have a table with the following information:

IndexIterationPoints
13819
23916
34013
44115
54216
6

43

16

 

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

IndexIterationAverage
140 = (19+16+13) / 3
241= (16+13+15) / 3
342= (13+15+16) / 3
443= (15+16+16) / 3

 

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

 

Please help

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
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.

vcazhengmsft_0-1654583801515.png

 

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

View solution in original post

2 REPLIES 2
v-cazheng-msft
Community Support
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.

vcazhengmsft_0-1654583801515.png

 

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

amitchandak
Super User
Super User

@donatoath ,

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors