cancel
Showing results 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

## For and While Loops in DAX

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
// Add a running total of demand to each row
// 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

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.

Tracking counter

Hi Greg,

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)

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

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

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

@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 __table1 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=1),"Value",[Value])
VAR __table2 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=2),"Value",[Value])
VAR __table3 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=3),"Value",[Value])
VAR __table4 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=4),"Value",[Value])
VAR __table5 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=5),"Value",[Value])
VAR __table6 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=6),"Value",[Value])
VAR __table7 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=7),"Value",[Value])
VAR __table8 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=8),"Value",[Value])
VAR __table9 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=9),"Value",[Value])
VAR __table10 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=10),"Value",[Value])
VAR __table11 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=11),"Value",[Value])
VAR __table12 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),[Value]=12),"Value",[Value])
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.

@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

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

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

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

@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.

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())

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.

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

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

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

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
Top Kudoed Posts
Latest Articles
Archives