cancel
Showing results for 
Search instead for 
Did you mean: 

Previous Value aka "Recursion"

Super User
421 Views
Highlighted
Super User
Super User

Previous Value aka "Recursion"

For additional information, see this blog post.

 

OK, I know what you are thinking, if there is anything more impossible than "for" and "while" loops in DAX, it is recursion. Whoever has faught with the ol' "circular reference" error in DAX knows that DAX hates recursion. And so it is, true recursion does not exist in DAX. However, ever since I published my article on For and While Loops in DAX, I got it into my head that perhaps I could use the same sort of technique for emulating "for" and "while" loops to also emulate recursion. Believe, me, this has been a dream of mine for quite some time as I first encountered the issue when writing about Kaplan Meier Survival Curves in Power BI and then again with Runge-Kutta and the Limits of DAX. Whenever I encounter it, I typically have to resort to Power Query "M" code as in my Fun with Graphing in Power BI series.

 

So, long story short, it is actually possible to do a quasi-psuedo emulation of recursion in DAX. It's brutally manual, is sort-of kind-of similar to "for" and "while" loop emulation and I actually kind of used the technique when dealing with Kaplan Meier Survival Curves, but if you ever get into a situation where you absolutely have to be able to use a dynamic "previous value" in DAX, here's a way to do it.

 

As an example, I used this technique to calculate the classicly recursive Fibonacci sequence, but, you know, actually calculate, calculate it which is different than what I did in my Fibonacci Quick Measure. Here it is:

 

 

mFibonnaci = 
VAR __value = MAX([Value])
// Seed our table with initial values
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)
// For each "recursion" we need to add a row and calculate our value at each "step"
// using our previous values. 
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]))
// Combine all of our steps together
VAR __table = UNION(__table0a,__table1a,__table2a,__table3a,__table4a,__table5a,__table6a,__table7a,__table8a,__table9a,__table10a,__table11a,__table12a)
RETURN
// Filter down to the current step and return the correct calculated value
SUMX(FILTER(__table,[Value]=__value),[Fib])

 

 

And I was able to make it through this entire post without a single recursive joke!

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!