Forum Discussion
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.
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], "-" )
6 Replies
All iterator functions are For Loops in DAX. SUMX, MAXX, PRODUCTX etc. One could argue that SUMMARIZE, SUMMARIZECOLUMNS and GENERATE are For Loops too.
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).
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.
