DAX中的递归 [英] Recursion in DAX
问题描述
我什至不知道这是否有可能，但是我希望能够创建一个计算列，其中每一行都取决于其上方的行。
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.
一个典型的例子是斐波那契序列，其中该序列由递归关系 F（n）= F（n-1）+ F（n-2）和种子 F（1）= F（2） = 1 。
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.
在表格中，
Index Fibonacci
----------------
1 1
2 1
3 2
4 3
5 5
6 8
7 13
8 21
9 34
10 55
... ...
我希望能够将 Fibonacci 列构造为
现在，我知道斐波那契数列有一个很好的封闭形式，我可以在其中定义
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)
或使用帕斯卡三角形的浅对角线：
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).
在Excel中，这很容易做到。您将编写这样的公式
In Excel, this is easy to do. You would write a formula like this
A3 = A2 + A1
或采用R1C1表示法
= 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]
)
)
给出错误信息
检测到循环依赖项：Table1 [Fibonacci]。
A circular dependency was detected: Table1[Fibonacci].
编辑：
在Microsoft SQL Server中的表格建模中描述了DAX的Marco Russo和Alberto Ferrari的Analysis Services，并包括以下段落：
In the book Tabular Modeling in Microsoft SQL Server Analysis Services by Marco Russo and Alberto Ferrari, DAX is described and includes this paragraph:
作为纯函数式语言，DAX没有强制性语句，但它利用称为 iterators 的特殊功能，这些功能为给定表表达式的每一行执行特定的表达式。这些参数与函数语言中的lambda表达式非常接近。但是，组合它们的方式存在局限性，因此我们不能说它们对应于通用lambda表达式定义。尽管具有功能性，但DAX不允许您定义新功能并且不提供递归。
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.
编辑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:
推荐答案
基于您的第一个样本数据集，对我来说，它看起来像是 种类的Cummulativ e Total，很可能可以使用WINDOW函数在SQL中轻松计算-我尝试了几件事，但目前还没有结果。我没有充分利用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.
编辑：在仔细回顾斐波那契数列时，发现我的 SQL 代码进行累积比较是不正确的。您可以阅读SO帖子如何生成斐波那契数列，它有一些优点我测试的 SQL Fibonacci 答案；尤其是 N J的帖子-14年2月13日回答。我不确定 DAX斐波那契递归函数的功能。
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代码（不太正确）：
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
结果集：
+------+-------------+-----------+
| 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累积：
可以使用DAX帮助计算累计总数的链接- https：//www.daxpatterns。 com / cumulative-total / 。这是文章中的一些示例代码。
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中的递归的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
