Greg_Deckler
Super User
‎03-04-2019 06:29 AM

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
Super User
‎03-04-2019 07:50 AM

Tracking counter

 

Web Analytics
0
‎03-05-2019 09:02 AM

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

0
‎03-05-2019 09:45 AM

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

Super User
‎03-05-2019 11:28 AM

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

 

image.png

0
Super User
‎03-05-2019 11:33 AM

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

Super User
‎03-05-2019 11:57 AM

@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

 

‎03-05-2019 12:21 PM

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

 

 

0
‎03-05-2019 04:03 PM

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

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

0
Super User
‎03-05-2019 05:31 PM

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

0
Helper III
‎07-31-2019 02:46 AM

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.

0
Anonymous
‎03-25-2020 10:55 AM

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.
0
‎07-09-2020 10:17 AM

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

0
‎03-21-2022 07:39 AM

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

0
Anonymous
‎06-13-2022 05:36 PM

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 

0
‎11-28-2023 06:50 AM

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 QtyTotal_On_Order_QtyTotal_Forecast_QtyTotal_On_Hand_Inv_QtyRunning On HandRunningSchedTotalQty running total in Week_EndWeek_EndEnding Inventory with productionEnding inventory no Productioncount above 0 DOS        
240035044810511051240044812/02/23300360330031 768        
  372 1051240082012/09/23263123126311 7612631       
  625 10512400144512/16/232006-39420061 75420062006      
1400 1092 10513800253712/23/232314-14869141 78791491423147    
  271 10513800280812/30/232043-17576431 7806436432043720437  
1200 135 10515000294301/06/243108-18925081 7113508508190871908731087
  157 10515000310001/13/242951-20493511 7 351351175171751729517
  175 10515000327501/20/242776-22241761 7 176176157671576727767
  136 10515000341101/27/242640-2360401 7 4040144071440726407
  221 10515000363202/03/242419-2581-18100.8190055 -181-181121971219724197
  231 10515000386302/10/242188-2812-4120   -412-4129887988721887
  245 10515000410802/17/241943-3057-6570   -657-6577437743719437
  222 10515000433002/24/241721-3279-8790   -879-8795217521717217
  240 10515000457003/02/241481-3519-11190   -1119-11192817281714817
  197 10515000476703/09/241284-3716-13160   -1316-131684784712847
  190 10515000495703/16/241094-3906-15060    -1506-1063-106310947
  190 10515000514703/23/24904-4096-16960     -296 -296 9047
  190 10515000533703/30/24714-4286-18860       -486 7147
  190 10515000552704/06/24524-4476-20760         5247
  190 10515000571704/13/24334-4666-22660         3347
  190 10515000590704/20/24144-4856-24560         1447
  190 10515000609704/27/24-46-5046-26460         -461
  190 10515000628705/04/24-236-5236-28360         -236 
  190 10515000647705/11/24-426-5426-30260         -426 
                     -426 
0