cancel
Showing results for 
Search instead for 
Did you mean: 

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

Comments

Tracking counter

 

Web Analytics

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

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
115
215
315
415
515

 

The while loop code works as explained

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

 

image.png

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

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