For and While Loops in DAX

[Greg_Deckler]

Introduction
One of the first things that befuddles people that know other coding languages when learning DAX is the absence of constructs for traditional “for” and “while” loops. This is a well understood limitation and just generally accepted by people who have learned DAX. However, when answering a recent Power BI Community forums post, I realized that I was using a technique that was essentially a proxy for a traditional “while” loop. Hence the inspiration for this article. This article has two parts, a theoretical (useless) part that explains the concept and a practical (useful) part that puts that theory into practice.

The Theoretical (Useless) Part
The For Loop
Let’s start with a simple for loop. As everyone learned in Programming 101, a “for” loop is used to repeat a specific block of code a known number of times. In a traditional programming language, the classic for loop example looks something like this:

int n = 5;
int sum = 0;
for(int i = 1; i <= n; i++)
{
sum += i;
}

Here we define a couple of variables, n as the limit to our for loop and sum, a variable to keep track of our total as we iterate over our loop. An additional variable i keeps track of how many times we iterate through our loop. Within the loop we simply add the current iteration number of our loop to our sum. In the case provided above, the value of the variable sum ends up being the result of 5 consecutive iterations, (0+1), (1+2), (3+3), (6+4), (10+5) = 15.

The DAX equivalent of this “for” loop is:

For Loop =
// Provide some starting values
VAR __n = 5
VAR __sum = 0
// Generate a "loop table", this will emulate a for loop for i=1 to some number
VAR __loopTable = GENERATESERIES(1,__n)
// Add in our calculated sum, emulating calculations done as iterations over the loop
VAR __loopTable1 = ADDCOLUMNS(__loopTable,"__sum",__sum + SUMX(FILTER(__loopTable,[Value]<=EARLIER([Value])),[Value]))
// Determine our MAX interation, the maximum value for "i"
VAR __max = MAXX(__loopTable1,[Value])
RETURN
// Return the value associated with the maximum value of "i" which is the last iteration in our "loop"
MAXX(FILTER(__loopTable1,[Value]=__max),[__sum])

Let’s walk through this a bit. We start with the same variable definitions for __n and __sum. We then create a table using GENERATESERIES that will serve as a proxy for our for “loop”. In a very DAX kind of way, we are essentially using each row as an iteration through the “loop”. We then add a column to this table that serves to emulate the calculations that would occur within each iteration of the traditional for loop above. This column essentially takes into account previous “loop” iterations by performing a SUMX of our intermediate column over the current and previous rows. Finally, we determine the maximum value of our iteration variable (i) using MAXX and then return the value of our __sum column for that row.

The answer that comes back is indeed 15, demonstrating the equivalency of this technique to a traditional for loop. Indeed, if one instead returns __lookupTable1 in a table, one would see this:

Value	__sum
1	1
2	3
3	6
4	10
5	15

Here we see that the value of the __sum column is indeed what we would expect for each iteration through our loop from our example above.

The While Loop
Now let’s tackle the “while” loop. Again, from Programming 101, we all know that in a “while” loop, a condition is evaluated first and if it returns true then the statements inside the “while” loop execute. When the condition returns false, the control comes out of loop and jumps to the next statement after the “while” loop.

int i=10;
while(i>1){
i--;
}

Therefore, the above code should iterate through the loop 9 times, decrementing our loop iterator (i) by one each time. On the 10th time, i=1 and thus this kicks the program out of the loop.

The DAX equivalent for “while” loop is:

While Loop =
// Provide some starting value via user input
VAR __i = 10
// Generate a "loop table", this will emulate a while loop
VAR __loopTable = GENERATESERIES(1,__i)
// Add in our calculated value, emulating calculations done as iterations over the loop
VAR __loopTable1 = ADDCOLUMNS(__loopTable,"__i",[Value] - 1)
RETURN
COUNTROWS(FILTER(__loopTable1,[__i]>1))+1

This DAX formula returns the expected 9 “iterations”. This construct is very similar to the “for” loop above. The big difference is the checking for our “boundary case”, when we should kick out of the loop. Here we FILTER our “loop” table according to our boundary case and add one. The adding of one in this case is required since we aren’t truly “checking” each time we go through the loop. In fact, if one were to look inside at __loopTable1, one would see this:

Value	__i
1	0
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9

The Practical (Useful) Part
OK, so enough with the theory. Obviously, those theoretical examples are a long way of doing a whole bunch of nothing! So the real question becomes, can we put this theory to use for something practical? As it so happens, the answer is yes!

In the forum post that I mentioned earlier, the question being asked was essentially a question about how many days it would take for a specified inventory to run out given a certain forecasted demand per week. So basically, the source data table looked like this:

Week	Demand	Ending on hand Inventory
Monday, February 4, 2019	0	49320
Monday, February 11, 2019	10819	38501
Monday, February 18, 2019	8114	39027
Monday, February 25, 2019	7717	31310
Monday, March 4, 2019	6923	24387
Monday, March 11, 2019	6923	77944
Monday, March 18, 2019	6923	71021
Monday, March 25, 2019	6924	81377
Monday, April 1, 2019	6000	75377
Monday, April 8, 2019	7200	68177
Monday, April 15, 2019	7200	60977
Monday, April 22, 2019	7200	53777
Monday, April 29, 2019	4118	49659
Monday, May 6, 2019	2577	47082
Monday, May 13, 2019	2577	44505
Monday, May 20, 2019	2577	41928
Monday, May 27, 2019	4411	37517
Monday, June 3, 2019	8077	29440
Monday, June 10, 2019	8077	30003
Monday, June 17, 2019	8077	21926
Monday, June 24, 2019	8077	13849
Monday, July 1, 2019	6666	15823
Monday, July 8, 2019	6667	17796
Monday, July 15, 2019	6667	11129
Monday, July 22, 2019	6666	13103
Monday, July 29, 2019	4431	8672
Monday, August 5, 2019	2194	6478
Monday, August 12, 2019	2194	12924

In order to answer this question, it is necessary to iterate over the table for each week, decrementing the current inventory on hand until a negative value is reached, indicating that inventory has run out. Sounds like a “while” loop. The solution indeed looks very similar to our theoretical “while” loop from above.

Days of Supply =
// Get the current week and inventory for the current row
VAR __week = MAX([Week])
VAR __inventory = MAX([Ending on hand Inventory])
// Create a table of all weeks greater than the current week
VAR __table = FILTER(ALL(Inventory),[Week]>__week)
// Add our current inventory from above to each row
VAR __table1 = ADDCOLUMNS(__table,"__start",__inventory)
// Add a running total of demand to each row
VAR __table2 = ADDCOLUMNS(__table1,"__demand",SUMX(FILTER(__table1,[Week]<=EARLIER([Week])),[Demand]))
// Add the difference in start versus the running total of demand to each row
VAR __table3 = ADDCOLUMNS(__table2,"__left",[__start] - [__demand])
// Create a table that only has the positive rows
VAR __table4 = FILTER(__table3,[__left]>=0)
// With only the positive rows, the MIN is the last row before demand runs out
VAR __min = MINX(__table4,[__left])
// Therefore, our base days is the number of rows in this table * 7
VAR __baseDays = COUNTROWS(__table4)*7
// Grab the MAX value of the negative rows, this is the row right after our inventory runs out
VAR __max = MAXX(FILTER(__table3,[__left]<0),[__left])
// Divide the row right before the inventory ran out by the sum of the absolute values of right before and after
// the inventory ran out. This is the percentage of days in that week before inventory ran out. multiply this by 7
// and this is the number of days in that week before inventory ran out
VAR __extraDays = __min / (__min + ABS(__max)) * 7
RETURN
__baseDays + __extraDays

The code above is pretty well documented so we won’t spend additional time explaining it. We will simply point out that this is a practical example of implementing what is essentially a “while” loop in DAX. This solution is posted to the Power BI Quick Measures Gallery as “Days of Supply“. Check it out!

Conclusion
While it is true that the DAX language utterly lacks any semblance of traditional “for” and “while” loop constructs, with a little creative DAX “for” and “while” loops can be emulated to solve real world problems.

[Greg_Deckler]

Tracking counter

[toshiro10]

Hi Greg,

Thanks a lot for this article, it's really useful !

I do have a question please : When you add a column, can you get the previous item in the column you're creating ?

It might seem weird but : I need that the first value of the column to have a different treatment than the others and for all the other lines, to use the previous value of the column.

I was thinking about something like this :

VAR __n =5
VAR __loopTable = GENERATESERIES(1;__n)

VAR __loopTable1 = ADDCOLUMNS(__loopTable;"__TotalActif";
             IF([Value] = 1; 'Liste'[Total_Flux_Actualise]- 1000; LOOKUPVALUE([__TotalActif] ; [Value] ; [Value]-1)))

But it's not doable this way...

Do you think there is any chance to do that ?

Best regards,

Abdel

[RedShirt]

When I use your code and run it in Dax Studio like this:

Evaluate
// For Loop =
// Provide some starting values
VAR __n = 5
VAR __sum = 0
// Generate a "loop table", this will emulate a for loop for i=1 to some number
VAR __loopTable = GENERATESERIES(1,__n)
// Add in our calculated sum, emulating calculations done as iterations over the loop
VAR __loopTable1 = ADDCOLUMNS(__loopTable,"__sum",__sum + SUMX(FILTER(__loopTable,[Value]<=EARLIER([Value])),[Value]))
// Determine our MAX interation, the maximum value for "i"
VAR __max = MAXX(__loopTable1,[Value])
RETURN
// Return the value associated with the maximum value of "i" which is the last iteration in our "loop"
//MAXX(FILTER(__loopTable1,[Value]=__max),[__sum])
__loopTable1

I get the following result

Value	__sum
1	15
2	15
3	15
4	15
5	15

The while loop code works as explained

[Greg_Deckler]

@RedShirt  - Hmm, not sure about DAX Studio, this is a screen shot from Power BI Desktop.

[Greg_Deckler]

@toshiro10 - Well, I've been thinking about recursion and "previous value" since I posted this. It's not easy, DAX hates recursion and anything that involves "previous value" and such has this kind of problem. However, I did sucessfully create a measure to calculate the Fibonacci series that I was going to post another article about. It's brutally manual, but has some potential applications and follows a similar pattern.

So, first, create a table like this:

Fibonacci = GENERATESERIES(0,12,1)

Then this measure:

mFibonnaci = 
VAR __value = MAX([Value])
VAR __table0 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=0),"Value",[Value])
VAR __table0a = ADDCOLUMNS(__table0,"Fib",0)
VAR __table1 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=1),"Value",[Value])
VAR __table1a = ADDCOLUMNS(__table1,"Fib",1)
VAR __table2 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=2),"Value",[Value])
VAR __table2a = ADDCOLUMNS(__table2,"Fib",SUMX(UNION(__table0a,__table1a),[Fib]))
VAR __table3 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=3),"Value",[Value])
VAR __table3a = ADDCOLUMNS(__table3,"Fib",SUMX(UNION(__table1a,__table2a),[Fib]))
VAR __table4 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=4),"Value",[Value])
VAR __table4a = ADDCOLUMNS(__table4,"Fib",SUMX(UNION(__table2a,__table3a),[Fib]))
VAR __table5 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=5),"Value",[Value])
VAR __table5a = ADDCOLUMNS(__table5,"Fib",SUMX(UNION(__table3a,__table4a),[Fib]))
VAR __table6 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=6),"Value",[Value])
VAR __table6a = ADDCOLUMNS(__table6,"Fib",SUMX(UNION(__table4a,__table5a),[Fib]))
VAR __table7 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=7),"Value",[Value])
VAR __table7a = ADDCOLUMNS(__table7,"Fib",SUMX(UNION(__table5a,__table6a),[Fib]))
VAR __table8 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=8),"Value",[Value])
VAR __table8a = ADDCOLUMNS(__table8,"Fib",SUMX(UNION(__table6a,__table7a),[Fib]))
VAR __table9 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=9),"Value",[Value])
VAR __table9a = ADDCOLUMNS(__table9,"Fib",SUMX(UNION(__table7a,__table8a),[Fib]))
VAR __table10 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=10),"Value",[Value])
VAR __table10a = ADDCOLUMNS(__table10,"Fib",SUMX(UNION(__table8a,__table9a),[Fib]))
VAR __table11 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=11),"Value",[Value])
VAR __table11a = ADDCOLUMNS(__table11,"Fib",SUMX(UNION(__table9a,__table10a),[Fib]))
VAR __table12 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=12),"Value",[Value])
VAR __table12a = ADDCOLUMNS(__table12,"Fib",SUMX(UNION(__table10a,__table11a),[Fib]))
VAR __table = UNION(__table0a,__table1a,__table2a,__table3a,__table4a,__table5a,__table6a,__table7a,__table8a,__table9a,__table10a,__table11a,__table12a)
RETURN
SUMX(FILTER(__table,[Value]=__value),[Fib])

So, one way to preserve "previous value" or othewise seed a "looping" calculation.

[Greg_Deckler]

@RedShirt  - I posted my For loop out to the Quick Measures gallery along with the PBIX file for you to review.

https://community.powerbi.com/t5/Quick-Measures-Gallery/For-Loop/m-p/637531

[RedShirt]

OK that's weird. The For Loop code works properly when I connect Dax Studio to a PBI model but not when I connect to a Tabular model at 1400 compatibility level. I need to test this on the latest CTP

[RedShirt]

Also in your FOR loop logic, there is no need to have __sum

You're just adding zero to the result from the Sumx()

[Greg_Deckler]

@RedShirt - Agreed in the specific use case covered in the example. But, that covers you if you set the initial sum to something other than zero. Try it out by setting it equal to 10, you should end up with 25.

[patoduck]

Hi Greg,

The big issue here is that we need Loops in Power Query, the hack using a function is ok but it has a very big limitation, you cannot use it in SSIS so if you want to scale up the logic created by an Analyst, you are not able to do it.

Not sure if Microsoft will eventually solve this thing, but at least REST, web api are off limits in SSIS Power Query source.

[Anonymous]

Hi Greg,

Hoping you can help me apply this to a stratified sampling problem that I am trying to solve. Within a table of 7800 rows I have identified 350 different strata. I need to return a table containing a random sampling of 10% of each strata i.e. if strata 1 has 30 rows I get a random sample of 3. I wrote the following:

Sample =

VAR NewTable = FILTER('ESStrata', [ID Code]= 1)

VAR SampleSize = 0.1*COUNTROWS(NewTable)

VAR RandTable = ADDCOLUMNS(NewTable, "Rand", RAND())

RETURN TOPN(SampleSize, RandTable, [Rand])

This gives me the correct sample for the specified strata. However, I need to run it for strata 1 to 350 and have the results returned in one table.

[hfeldman]

Hi Greg, great write up.
Could you please explain a little more what the [VALUE] represents in the ADDCOLUMNS() and MAXX() functions? In the theory example you have [VALUE] but in the days of supply example you have [WEEK] and [DEMAND] in its place. I'm confused as to how exactly where this [VALUE] comes from and what its function is. 

Thanks

[Nallim]

Hi, how do i do this for a cycle?

int n = 3;
double price = 500;
double inflation = 1.02;
for(int i = 1; i <= n; i++)
{
price *= inflation;
}

result is 530.604

Thanks

[EdgarMen099]

Hi everyone, 

I just wonder if anyone can help me by going from the Theretical to Dax. 

I have this:
If n>=1;
         A = B-C

for i = 2 : n ;

         B = A + ( i - 1 ) * C

end 

[clowery]

Does this work for evaluating  over several different items at several different manufacturing plants and Distribution centers for each plant?  I also need a way to evaluate for future dates, but only include production if the days of supply is being evaluated for that date, not to include the production as inventory when evaluating for future dates as being included int eh calculation for a date.  Example.  When evaluating for 12/02 - include the production  for 12/2 and all future demand with the on hand inventory, but not include the future prodution in the calculation and this is for only one item.  I have many items that would need to be calcualted and re-evaluated each future week.  So, on 12/23 I would need to include the production in the evaluation for DOS for that week, but not include the future production in the claculation for that week.  Is there  a better way to do this now that we have the use of offset or windows functions?  I need the DOS that is calcualted below to be the result for eachweek.  I also probably have to account for the days of this week that have passed already, but that isn't important at this point.  Will the table created be created for each item?

Prod Qty	Total_On_Order_Qty	Total_Forecast_Qty	Total_On_Hand_Inv_Qty	Running On Hand	RunningSchedTotal	Qty running total in Week_End	Week_End	Ending Inventory with production	Ending inventory no Production	count above 0		DOS
2400	350	448	1051	1051	2400	448	12/02/23	3003	603	3003	1		7	68
		372		1051	2400	820	12/09/23	2631	231	2631	1		7	61	2631
		625		1051	2400	1445	12/16/23	2006	-394	2006	1		7	54	2006	2006
1400		1092		1051	3800	2537	12/23/23	2314	-1486	914	1		7	87	914	914	2314	7
		271		1051	3800	2808	12/30/23	2043	-1757	643	1		7	80	643	643	2043	7	2043	7
1200		135		1051	5000	2943	01/06/24	3108	-1892	508	1		7	113	508	508	1908	7	1908	7	3108	7
		157		1051	5000	3100	01/13/24	2951	-2049	351	1		7		351	351	1751	7	1751	7	2951	7
		175		1051	5000	3275	01/20/24	2776	-2224	176	1		7		176	176	1576	7	1576	7	2776	7
		136		1051	5000	3411	01/27/24	2640	-2360	40	1		7		40	40	1440	7	1440	7	2640	7
		221		1051	5000	3632	02/03/24	2419	-2581	-181	0	0.819005	5		-181	-181	1219	7	1219	7	2419	7
		231		1051	5000	3863	02/10/24	2188	-2812	-412	0				-412	-412	988	7	988	7	2188	7
		245		1051	5000	4108	02/17/24	1943	-3057	-657	0				-657	-657	743	7	743	7	1943	7
		222		1051	5000	4330	02/24/24	1721	-3279	-879	0				-879	-879	521	7	521	7	1721	7
		240		1051	5000	4570	03/02/24	1481	-3519	-1119	0				-1119	-1119	281	7	281	7	1481	7
		197		1051	5000	4767	03/09/24	1284	-3716	-1316	0				-1316	-1316	84	7	84	7	1284	7
		190		1051	5000	4957	03/16/24	1094	-3906	-1506	0					-1506	-106	3	-106	3	1094	7
		190		1051	5000	5147	03/23/24	904	-4096	-1696	0						-296		-296		904	7
		190		1051	5000	5337	03/30/24	714	-4286	-1886	0								-486		714	7
		190		1051	5000	5527	04/06/24	524	-4476	-2076	0										524	7
		190		1051	5000	5717	04/13/24	334	-4666	-2266	0										334	7
		190		1051	5000	5907	04/20/24	144	-4856	-2456	0										144	7
		190		1051	5000	6097	04/27/24	-46	-5046	-2646	0										-46	1
		190		1051	5000	6287	05/04/24	-236	-5236	-2836	0										-236
		190		1051	5000	6477	05/11/24	-426	-5426	-3026	0										-426
																					-426