VAR _table3 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),=3),"Value",) VAR _table2a = ADDCOLUMNS(_table2,"Fib",SUMX(UNION(_table0a,_table1a),)) VAR _table2 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),=2),"Value",) VAR _table1a = ADDCOLUMNS(_table1,"Fib",1) VAR _table1 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),=1),"Value",) VAR _table0a = ADDCOLUMNS(_table0,"Fib",0) VAR _table0 = SELECTCOLUMNS(FILTER(ALL('Fibonacci'),=0),"Value",) So, first, create a table like this: Fibonacci = GENERATESERIES(0,12,1) It's brutally manual, but has some potential applications and follows a similar pattern.
However, I did sucessfully create a measure to calculate the Fibonacci series that I was going to post another article about. It's not easy, DAX hates recursion and anything that involves "previous value" and such has this kind of problem. 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 - Well, I've been thinking about recursion and "previous value" since I posted this. This solution is posted to the Power BI Quick Measures Gallery as “ Days of Supply“. We will simply point out that this is a practical example of implementing what is essentially a “while” loop in DAX.
The code above is pretty well documented so we won’t spend additional time explaining it. VAR _extraDays = _min / (_min + ABS(_max)) * 7 and this is the number of days in that week before inventory ran out This is the percentage of days in that week before inventory ran out. Divide the row right before the inventory ran out by the sum of the absolute values of right before and after
Grab the MAX value of the negative rows, this is the row right after our inventory runs out Therefore, our base days is the number of rows in this table * 7 With only the positive rows, the MIN is the last row before demand runs out VAR _table2 = ADDCOLUMNS(_table1,"_demand",SUMX(FILTER(_table1,=0) Add a running total of demand to each row VAR _table1 = ADDCOLUMNS(_table,"_start",_inventory) Add our current inventory from above to each row VAR _table = FILTER(ALL(Inventory),>_week) Create a table of all weeks greater than the current week Get the current week and inventory for the current row The solution indeed looks very similar to our theoretical “while” loop from above. 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. So basically, the source data table looked like this: 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. 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 fact, if one were to look inside at _loopTable1, one would see this: The adding of one in this case is required since we aren’t truly “checking” each time we go through the loop. Here we FILTER our “loop” table according to our boundary case and add one. The big difference is the checking for our “boundary case”, when we should kick out of the loop. This construct is very similar to the “for” loop above. This DAX formula returns the expected 9 “iterations”. VAR _loopTable1 = ADDCOLUMNS(_loopTable,"_i", - 1)ĬOUNTROWS(FILTER(_loopTable1,>1))+1 Add in our calculated value, emulating calculations done as iterations over the loop Generate a "loop table", this will emulate a while loop Provide some starting value via user input On the 10th time, i=1 and thus this kicks the program out of the loop. Therefore, the above code should iterate through the loop 9 times, decrementing our loop iterator (i) by one each time. In a traditional programming language, the classic for loop example looks something like this: As everyone learned in Programming 101, a “for” loop is used to repeat a specific block of code a known number of times. This article has two parts, a theoretical (useless) part that explains the concept and a practical (useful) part that puts that theory into practice. 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. This is a well understood limitation and just generally accepted by people who have learned DAX. 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.