DAX with For/While Loop

[kmj97024]

Hi All,

I created some DAX code which I want it to work as a For/While loop. I used this page as a reference. (https://community.powerbi.com/t5/Community-Blog/For-and-While-Loops-in-DAX/ba-p/636314)

LOOP - HR Analysis = 
VAR IndexNum = 1
VAR TotalValue = 0
VAR LoopTable = GENERATESERIES(1, 1)
VAR LoopTable1 = ADDCOLUMNS(LoopTable, "SUM", TotalValue + SUMX(FILTER(LoopTable, [Value]<=EARLIER([Value])), 
                                                            [LOOP - HR Analysis Value]))
VAR MaxInteraction = MAXX(LoopTable1, [Value])
RETURN MAXX(FILTER(LoopTable1, [Value]=MaxInteraction), [SUM])

But I want this code to work something similar to below, where I can give the iterator value as a parameter for my measure used in the expression area.

As you can see I get a syntax error in this example though. Does anyone know if I can achieve the same behaviour, without an error?

Thank you!

[v-yangliu-msft · Accepted solution]

Hi @kmj97024 ,

You can try to use the following function：

LOOP - HR Analysis =
VAR IndexNum = 1
VAR TotalValue = 0
VAR LoopTable = GENERATESERIES(1, 1)
VAR LoopTable1 = ADDCOLUMNS(LoopTable, "SUM", TotalValue + SUMX(FILTER(LoopTable, [Value]<=EARLIER([Value])),
                                                          SUMX(FILTER(ALL('HR Analysis'),
                                                         'HR Analysis'[Index]=[Value]), [LOOP - HR Analysis Value])))
VAR MaxInteraction = MAXX(LoopTable1, [Value])
RETURN MAXX(FILTER(LoopTable1, [Value]=MaxInteraction), [SUM])

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@kmj97024 , add a filter or you have to use maxx(looptable, [Value])

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.