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.

Lineage 1 bot programmiren