Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
03-05-2019 14:13 PM - last edited 06-02-2020 14:40 PM
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 fought 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!
eyJrIjoiOWE3OTQ1NDItMGNkZC00M2QwLTkzOWMtZWM2NGQ5Nzg0MWExIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
Hey Greg,
Awesome post! Is it possible to do something similar, but with measures rather than columns?