Recursion in DAX

dax

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

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/

Recursion in DAX

Related

Recent Posts