Forum Discussion

kmj97024's avatar
kmj97024
New Member
4 years ago
Solved

DAX with For/While Loop

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!

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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