Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
@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.
Solved! Go to 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], "-" )
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.
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], "-" )
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], "-" )
All iterator functions are For Loops in DAX. SUMX, MAXX, PRODUCTX etc. One could argue that SUMMARIZE, SUMMARIZECOLUMNS and GENERATE are For Loops too.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |