Recursion in DAX

Question

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.

A classic example of this is the Fibonacci sequence, where the sequence is defined by the recurrence relationship F(n) = F(n-1) + F(n-2) and seeds F(1) = F(2) = 1.

In table form,

Index  Fibonacci
----------------
 1      1
 2      1
 3      2
 4      3
 5      5
 6      8
 7     13
 8     21
 9     34
10     55
...    ...

I want to be able to construct the Fibonacci column as a calculated column.

Now, I know that the Fibonacci sequence has a nice closed form where I can define

Fibonacci = (((1 + SQRT(5))/2)^[Index] - ((1 - SQRT(5))/2)^[Index])/SQRT(5)

or using the shallow diagonals of Pascal's triangle form:

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

but this is not the case for recursively defined functions in general (or for the purposes I'm actually interested in using this for).

---

In Excel, this is easy to do. You would write a formula like this

A3 = A2 + A1

or in R1C1 notation,

= R[-1]C + R[-2]C

but I just can't figure out if this is even possible in DAX.

Everything I've tried either doesn't work or gives a circular dependency error. For example,

Fibonacci = 
VAR n = [Index]
RETURN
IF(Table1[Index] <= 2,
    1,
    SUMX(
        FILTER(Table1,
            Table1[Index] IN {n - 1, n - 2}),
        Table1[Fibonacci]
    )
)

gives the error message

A circular dependency was detected: Table1[Fibonacci].

---

Edit:

In the book Tabular Modeling in Microsoft SQL Server Analysis Services by Marco Russo and Alberto Ferrari, DAX is described and includes this paragraph:

As a pure functional language, DAX does not have imperative statements, but it leverages special functions called iterators 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.

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 Parent-Child functions, which appear to be recursive in nature.

---

Edit 2:

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.

Here are a couple of examples where I use this workaround to sidestep recursive formulas:

How to perform sum of previous cells of same column in PowerBI

DAX - formula referencing itself

Answer 1

Based on your first sample dataset, it looks to me like a "sort of" Cummulative Total, which can probably calculated easily in SQL using WINDOW function-- I tried a couple things but nothing panned out just yet. I don't work with DAX enough to say if it can be done.

Edit: In reviewing a little closer the Fibonacci sequence, it turns out that my SQL code doing cumulative comparison is not correct. You can read the SO Post How to generate Fibonacci Series, and it has a few good SQL Fibonacci answers that I tested; in particular the post by N J - answered Feb 13 '14. I'm not sure of a DAX Fibonacci recursion function capability.

---

SQL Code (not quite correct):

DECLARE @myTable as table (Indx int)

INSERT INTO @myTable VALUES
    (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

SELECT
    Indx
    ,SUM(myTable.Indx) OVER(ORDER BY myTable.Indx ASC ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) -- + myTable.Indx
        AS [Cummulative]
    ,SUM(myTable.Indx) OVER(ORDER BY myTable.Indx ASC ROWS BETWEEN UNBOUNDED PRECEDING and 2 PRECEDING) 
        + SUM(myTable.Indx) OVER(ORDER BY myTable.Indx ASC ROWS BETWEEN UNBOUNDED PRECEDING and 1 PRECEDING) 
        AS [Fibonacci]
from @myTable myTable

Result Set:

+------+-------------+-----------+
| Indx | Cummulative | Fibonacci |
+------+-------------+-----------+
| 1    | 1           | NULL      |
+------+-------------+-----------+
| 2    | 3           | NULL      |
+------+-------------+-----------+
| 3    | 6           | 4         |
+------+-------------+-----------+
| 4    | 10          | 9         |
+------+-------------+-----------+
| 5    | 15          | 16        |
+------+-------------+-----------+
| 6    | 21          | 25        |
+------+-------------+-----------+
| 7    | 28          | 36        |
+------+-------------+-----------+
| 8    | 36          | 49        |
+------+-------------+-----------+
| 9    | 45          | 64        |
+------+-------------+-----------+
| 10   | 55          | 81        |
+------+-------------+-----------+

---

DAX Cummulative:

A link that could help calculate cumulative totals with DAX-- https://www.daxpatterns.com/cumulative-total/. And here is some sample code from the article.

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

Answer 2

DAX language doesn't support recursion.

It's also been written in a sqlbi's article about calculation groups

DAX is not recursive, so Calculation Groups do not allow recursion. This is a good idea for controlling performance, but it requires a different approach compared to certain techniques that are possible in MDX Script by leveraging recursion.

https://www.sqlbi.com/blog/marco/2019/03/01/calculation-groups-in-dax-first-impressions/