Forum Discussion
Solved
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...
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], "-" )