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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
smpa01
Super User
Super User

DAX forLoop

@AlexisOlson  is it possible to replicate a forLoop in DAX. I need a forLoop that works in filter context. I am not able to find anything that can do the following in DAX.

 

let
   src=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jcu5DQAxDAPBXhg7kHR+axHcfxsn0gmBHYCZMDS4r9o1cFvCBbs2HoTg1E71xw7+urqreTsmGALd7MmU8BiU+wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [index = _t, row = _t, Value = _t]),
    ct = Table.TransformColumnTypes(src,{{"index", Int64.Type}, {"row", Int64.Type}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(ct, "Custom", each let
 curr = [index],
 filt = Table.SelectRows(ct, (_)=> (_[index] >= curr)),
 terminator = Table.RowCount(filt)-1,
 forLoop = List.Generate(
     ()=> [ i =0, x = filt[Value]{i}, y= Text.From(filt[row]{i}), combins = 0+x],       
        each [combins]<=100,
        each [i=[i]+1, x= filt[Value]{i}, y= [y]&"-"&Text.From(filt[row]{i}), combins = [combins]+x],
        each [y]
 ),
 len = List.Count(forLoop)-1
 in if (len>=0) then forLoop{len} else null)
in
    #"Added Custom"

 

Thank you in advance.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
1 ACCEPTED SOLUTION

Custom DAX =
VAR i = [index]
VAR b =
    ADDCOLUMNS (
        FILTER ( Query1, [index] >= i ),
        "g",
            VAR i2 = [index]
            RETURN
                SUMX ( FILTER ( Query1, [index] >= i && [index] <= i2 ), [Value] )
    )
RETURN
    CONCATENATEX ( FILTER ( b, [g] <= 100 ), [row], "-" )

 

lbendlin_0-1714854546064.png

 

View solution in original post

6 REPLIES 6
smpa01
Super User
Super User

Great, but how do I apply this to my scenario is the challenge. I need a for Loop to work in a way so that for each row in the filter context it considers a table where filter(all(table), table[index]>= currentIndexVisible) and then keep on conctaenating table[row], till runningTotal of values <=100 (termination clause).

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

Please show the expected outcome based on the sample data you provided.


You can use the code from snippet to get the sample data.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Custom DAX =
VAR i = [index]
VAR b =
    ADDCOLUMNS (
        FILTER ( Query1, [index] >= i ),
        "g",
            VAR i2 = [index]
            RETURN
                SUMX ( FILTER ( Query1, [index] >= i && [index] <= i2 ), [Value] )
    )
RETURN
    CONCATENATEX ( FILTER ( b, [g] <= 100 ), [row], "-" )

 

lbendlin_0-1714854546064.png

 

Here's a slightly optimized version

 

Custom DAX = 
VAR i = [index]
VAR f =
    FILTER ( Query1, [index] >= i )
VAR g =
    ADDCOLUMNS (
        f,
        "rt", VAR i2 = [index] RETURN SUMX ( FILTER ( f, [index] <= i2 ), [Value] )
    )
RETURN
    CONCATENATEX ( FILTER ( g, [rt] <= 100 ), [row], "-" )
lbendlin
Super User
Super User

All iterator functions are For Loops in DAX. SUMX, MAXX, PRODUCTX etc. One could argue that SUMMARIZE, SUMMARIZECOLUMNS and GENERATE are For Loops too.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors