title: Recursion in DAX
body: <p>I don't know if this is even possible, but I'd like to be able to create a calculated column where each row is dependent on the rows above it.</p>
  
  <p>A classic example of this is the <a href="https://en.wikipedia.org/wiki/Fibonacci_number" rel="noreferrer">Fibonacci sequence</a>, where the sequence is defined by the recurrence relationship <code>F(n) = F(n-1) + F(n-2)</code> and seeds <code>F(1) = F(2) = 1</code>.</p>
  
  <p>In table form,</p>
  
  <pre><code>Index  Fibonacci
  ----------------
   1      1
   2      1
   3      2
   4      3
   5      5
   6      8
   7     13
   8     21
   9     34
  10     55
  ...    ...
  </code></pre>
  
  <p>I want to be able to construct the <code>Fibonacci</code> column as a calculated column.</p>
  
  <p>Now, I know that the Fibonacci sequence has a nice closed form where I can define</p>
  
  <pre><code>Fibonacci = (((1 + SQRT(5))/2)^[Index] - ((1 - SQRT(5))/2)^[Index])/SQRT(5)
  </code></pre>
  
  <p>or using the <a href="https://en.wikipedia.org/wiki/Fibonacci_number#Use_in_mathematics" rel="noreferrer">shallow diagonals of Pascal's triangle form</a>:</p>
  
  <pre><code>Fibonacci =
  SUMX (
      ADDCOLUMNS (
          SELECTCOLUMNS (
              GENERATESERIES ( 0, FLOOR ( ( [Index] - 1 ) / 2, 1 ) ),
              "ID", [Value]
          ),
          "BinomCoeff", IF (
              [ID] = 0,
              1,
              PRODUCTX (
                  GENERATESERIES ( 1, [ID] ),
                  DIVIDE ( [Index] - [ID] - [Value], [Value] )
              )
          )
      ),
      [BinomCoeff]
  )
  </code></pre>
  
  <p>but this is not the case for recursively defined functions in general (or for the purposes I'm actually interested in using this for).</p>
  
  <hr>
  
  <p>In Excel, this is easy to do. You would write a formula like this</p>
  
  <pre><code>A3 = A2 + A1
  </code></pre>
  
  <p>or in R1C1 notation,</p>
  
  <pre><code>= R[-1]C + R[-2]C
  </code></pre>
  
  <p>but I just can't figure out if this is even possible in DAX.</p>
  
  <p>Everything I've tried either doesn't work or gives a circular dependency error. For example,</p>
  
  <pre><code>Fibonacci = 
  VAR n = [Index]
  RETURN
  IF(Table1[Index] &lt;= 2,
      1,
      SUMX(
          FILTER(Table1,
              Table1[Index] IN {n - 1, n - 2}),
          Table1[Fibonacci]
      )
  )
  </code></pre>
  
  <p>gives the error message</p>
  
  <blockquote>
    <p>A circular dependency was detected: Table1[Fibonacci].</p>
  </blockquote>
  
  <hr>
  
  <p><strong>Edit:</strong></p>
  
  <p>In the book Tabular Modeling in Microsoft SQL Server Analysis Services by Marco Russo and Alberto Ferrari, DAX is described and includes this paragraph:</p>
  
  <blockquote>
    <p>As a pure functional language, DAX does not have imperative statements, but it leverages special functions called <em>iterators</em> that execute a certain expression for each row of a given table expression. These arguments are close to the lambda expression in functional languages. However, there are limitations in the way you can combine them, so we cannot say they correspond to a generic lambda expression definition. Despite its functional nature, DAX does not allow you to define new functions and does not provide recursion.</p>
  </blockquote>
  
  <p>It appears there is no straightforward way to do recursion. I do still wonder if there is a way to still do it indirectly somehow using <a href="https://msdn.microsoft.com/en-us/query-bi/dax/understanding-functions-for-parent-child-hierarchies-in-dax" rel="noreferrer">Parent-Child functions</a>, which appear to be recursive in nature.</p>
  
  <hr>
  
  <p><strong>Edit 2:</strong></p>
  
  <p>While general recursion doesn't seem feasible, don't forget that recursive formulas may have a nice closed form that can be fairly easily derived.</p>
  
  <p>Here are a couple of examples where I use this workaround to sidestep recursive formulas:</p>
  
  <p><a href="https://stackoverflow.com/questions/61257536/">How to perform sum of previous cells of same column in PowerBI</a></p>
  
  <p><a href="https://stackoverflow.com/questions/60641059">DAX - formula referencing itself</a></p>