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 the value associated with the maximum value of "i" which is the last iteration in our "loop"
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:
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.
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)
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:
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:
Ending on hand Inventory
Monday, February 4, 2019
Monday, February 11, 2019
Monday, February 18, 2019
Monday, February 25, 2019
Monday, March 4, 2019
Monday, March 11, 2019
Monday, March 18, 2019
Monday, March 25, 2019
Monday, April 1, 2019
Monday, April 8, 2019
Monday, April 15, 2019
Monday, April 22, 2019
Monday, April 29, 2019
Monday, May 6, 2019
Monday, May 13, 2019
Monday, May 20, 2019
Monday, May 27, 2019
Monday, June 3, 2019
Monday, June 10, 2019
Monday, June 17, 2019
Monday, June 24, 2019
Monday, July 1, 2019
Monday, July 8, 2019
Monday, July 15, 2019
Monday, July 22, 2019
Monday, July 29, 2019
Monday, August 5, 2019
Monday, August 12, 2019
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
__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.