Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.