Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Greg_Deckler

Previous Value (“Recursion”) in DAX

Introduction

First, let me be clear, there is no true recursion in DAX. Try as I might, and believe me I have tried every conceivable way possible, I have never found a way around the accursed “circular reference” error DAX inevitably throws whenever you even so much as think of doing anything remotely along the lines of recursion. Despite this cold, hard fact, I have never given up hope of one day finding a way; any way, around this maddening limitation. So, after I came up with a way to emulate “for” and “while” loops in DAX, I again started thinking about my old foe, recursion, and how I might be able to use a similar technique to best it/he/she once and for all. Here we go.

 

The Theoretical Part

First, let’s review what recursion is and how it is done in more traditional programming languages. There are lots of formal definitions out there but simply put, recursion is something that is defined in terms of itself. No, this is not the same as using the third person to refer to oneself but it can be similarly annoying and maddening. The classic example in mathematics is the Fibonacci series which is defined as:

 

Fib(0) = 0 as base case 1,
Fib(1) = 1  as base case 2,
For all integers  n > 1, Fib(n) := Fib(n − 1) + Fib(n − 2)

In other words, the value of the Fibonacci series for any number in the sequence other than the 1st or 2nd (0 and 1) is dependent upon the value of the Fibonacci function for the two previous numbers in the Fibonacci series. If this makes your head hurt, it’s OK, I’ve worked with individuals with Masters in Computer Science that couldn’t successfully pull off a recursive program. I’m not joking. Hint, globals = bad.

 

Let’s look at a piece of pseudo-code to see how recursion works in programming. The example below is a bit of code that would compute the factorial of a number.

 

int f(int n){
if (n <= 1)
       return 1;
else
       return n*f(n-1);
}
 
f(4)

OK, what you are seeing there is the definition of a function “f” that takes a single integer as input. A base case is defined that if the number is less than or equal to 1, then return 1, otherwise, the function call itself with an input of the current input minus 1. The base case ensures that the recursion does not loop indefinitely and by calling the function recursively with an input of the current input – 1 ensures that the base case is eventually reached. So, if we were to call this function with an input of 4, here is what would happen.

 

4 * f(3) -> 3 * f(2) -> 2 * f(1) -> 1

 

Therefore, 2*1 = 2 and then 3*2 = 6 and then 4*6 = 24.

 

You can picture this as if you are forming a hierarchy and once you get to the bottom, you start returning your value back up through the hierarchy, doing your computation along the way as you head back to the top. Once you are at the top, you have your answer. Thus, it is highly critical that as you recurse through the program, you have to keep track of the previous value at the level just “above” your next level. If this did not occur, you would not get the correct result. Hence, if you use a global variable to keep track of “n”…well, let’s just say things will not work out for you.

 

So, as I explained earlier, recursion is verboten in DAX. I first struggled with this when I attempted to create a Kaplan-Meier Survival Curve with Power BI. Tableau has a handy PREVIOUS_VALUE function for measures and this proved difficult to replicate in DAX. So I hacked up a work-around but it bugged me so much, I submitted an Idea for a PREVIOUSVALUE function in DAX that, ahem, nobody has ever voted for other than myself. Then, I fought it again with Runge-Kutta and the Limits of DAX. Again, I was able to concoct a work-around that I eventually made marginally more elegant but was never really satisfied. When I started doing fractals, I didn’t even bother with DAX, I just used M code, which also does not support “for” and “next” loops, but; perhaps oddly, does support recursion. So, I’ve been at this a while…

 

OK, back to present day. I had just found a way to essentially emulate “for” and “while” loops in DAX using something that DAX is really good at, tables. Hence, the thought struck me, was there somehow a similar solution in which I could effectively emulate recursion in DAX? I’ll let you decide but I’m starting to believe that I think that the likely answer is a definite, qualitative “maybe”.

 

The Practical Part

So let’s start with the question, is there a practical reason why we might need recursion, or to know the previous value of a measure when calculating a measure? Well, it definitely comes up from time-to-time. There are practical use cases for it, like this post or the Kaplan-Meier Survival Curve scenario above.

 

With this in mind as self-justification for my Pequod’ian quest to slay the recursed white whale, the plan was to again use a table row as a representative of each recursive loop iteration. However, given the nature of recursion, this ended up coming out a bit different and brutally more manual than I would have liked. But, if you really have to have a measure that essentially does dynamic “recursion”, then something like this will fit the bill. It’s code intensive, computational costly and downright; well, ugly, but it will get the job done.

 

To demonstrate, I chose our old friend Fibonacci. Yes, I’d previously danced with Fibonacci but that was more of a mathematical trick that allowed its computation. This time, I’d do it without the mathematical hocus pocus.

 

Here’s the code for the first 13 values of the Fibonacci series:

 

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])

Now remember, I said, “emulate” not actually do recursion, let’s keep that in mind here folks. The overriding architecture here is that we first grab the value of the Fibonacci value that we are trying to compute. We then build a base table that effectively represents our “base” case(s). Then we compute every iteration of our “recursive” procedure but for each iteration, we are actually building a single row in a separate table.

 

By creating a separate table for each “iteration”, we can effectively change up how our computed value gets computed, referring back to our base table or previously computed table rows to do so. Thus, we are “preserving” our previous values and feeding that input into the computation of our next “iteration”. If you feel like there are lots of finger quotes going on here, you would be correct. In any case, once we are done computing each of our iterations, we can then smash them all together into one big table using UNION and finally FILTER out our desired answer.

 

So, if you look at the two bold red lines above, the first just creates our table with the number in the Fibonacci sequence that we are computing. The next line essentially feeds the two previous rows into the computation of this next number, adding the two numbers from the previous iterations together. This process continues for each iteration until we get tired of cutting, pasting and incrementing our variable numbers.

 

Conclusion

It’s brutal, it’s manual, it’s ugly and it doesn’t much resemble recursion, but this is probably the best solution I have come up with to date that can serve as a general purpose way of dealing with situations where you have to have a dynamic, recursive measure in DAX. If you have a better way, trust me, I would love to learn about it so leave a comment below!

 

If you want the PBIX that implements this, check out my Quick Measure Gallery entry, Previous Value aka “Recursion”. Yes, again with the finger quotes… And, if you are perhaps struggling a bit with the concept of recursion, then you should definitely check out this article.

 

Comments