Power BI: 使用计算列创建关系中的循环依赖问题

发布于

文章背景： 在表缺少主键无法直接创建关系，或者需要借助复杂的计算才能创建主键的情况下，可以利用计算列来设置关系。在基于计算列创建关系时，循环依赖经常发生。下面先介绍一个示例，然后讲解循环依赖产生的原因，以及如何避免空行依赖。

1 示例2 原因分析3 避免空行依赖

1 示例

有这样一个场景：根据产品的价格列表对产品进行分组。产品的价格有很多不同的数值，一种常用的做法是将价格划分成不同的区间。例如下图所示的配置表。

现在对价格区间的键值进行反规范化，然后根据这个新的计算列建立一个物理关系。下图是预期要建立的数学模型。

下面是Sales表中检索键值（PriceRangeKey）的代码：

代码语言：javascript
复制
PriceRangeKey = 
VAR FilterPriceRanges =
    FILTER (
        PriceRanges,
        AND (
            PriceRanges[MinPrice] <= Sales[Net Price],
            PriceRanges[MaxPrice] > Sales[Net Price]
        )
    )
VAR Result =
    CALCULATE (
        IFERROR (
            VALUES( PriceRanges[PriceRangeKey] ), 
            BLANK ()
        ),
        FilterPriceRanges 
    )
RETURN
    Result

值得注意的是，这里使用VALUES来检索单个值。VALUES返回一个表，而不是一个值。每当一个表中包含单行和单列时，如果表达式需要的话，这个表就会被自动转换为标量值。

当试图在新创建的PriceRangeKey列的基础上建立PriceRanges表和Sales表之间的关系时，将由于循环依赖关系而导致错误。

在这个例子中，修复方法很简单：使用DISTINCT代替VALUES。一旦改用DISTINCT，就可以正常创建关系了。结果如下图所示。

正确设置关系后，可以按价格区间切片了。

下面对因为与计算列建立关系而出现的循环依赖进行分析，包括为什么DISTINCT可以消除循环依赖。

2 原因分析

让我们回顾一下计算列公式的简写版本（Sale表的PriceRangeKey列）：

代码语言：javascript
复制
PriceRangeKey = 
CALCULATE (
    VALUES( PriceRanges[PriceRangeKey] ), 
    FILTER (
        PriceRanges,
        AND (
            PriceRanges[MinPrice] <= Sales[Net Price],
            PriceRanges[MaxPrice] > Sales[Net Price]
        )
    )
)

（1）对于强一对多关系，表的扩展总是像一端方向进行。为了防止关系出现无效记录，位于关系一端的表可能会添加空行。

（2）DAX中的依赖关系有两种类型：公式依赖（或引用依赖）和空行依赖。在我们的例子中，情况是这样的：

  • Sales[PriceRangeKey]依赖PriceRanges表，既因为公式中引用了PriceRanges表（引用依赖），又因为使用了VALUES函数，可能会返回额外的空行（空行依赖）。
  • PriceRanges表依赖Sales[PriceRangeKey]，只是因为空行的原因。改变Sales[PriceRangeKey]的值，可能会导致PriceRanges表产生空行。

为了中断循环依赖关系链，只要打破Sales[PriceRangeKey]对PriceRanges表的空行依赖即可。通过确保公式中使用的所有函数不依赖空行可以实现这一目的。

当多端的一个值不存在于一端内时，VALUES返回的结果会把空行包含进来。而如果使用DISTINCT，无论额外的空行是否存在，DISTINCT始终产生相同的结果。总而言之，VALUES依赖空行，DISTINCT不依赖空行。

如果使用DISTINCT代替VALUES，那么Sales[PriceRangeKey]不再依赖空行。最终的结果是：PriceRanges表依赖于Sales[PriceRangeKey]的空行，而Sales[PriceRangeKey]依赖于PriceRanges的引用。由于两个依赖关系没有形成闭环，所以循环依赖消失了，可以创建关系。

3 避免空行依赖

创建可能用于设置关系的计算列时，都需要注意以下细节：

  • 使用DISTINCT 代替VALUES。
  • 使用ALLNOBLANKROW代替ALL。
  • 谨防CALCULATE直接使用布尔表达式作为筛选器参数。

下面解释最后一点——注意CALCULATE。例如，观察下面的表达式：

代码语言：javascript
复制
= CALCULATE( 
    MAX( Customer[YearlyIncome]), 
    Customer[Education]="High School"
  )

乍一看，这个公式好像不依赖Customer表中的空行。事实上，它却是依赖的。原因是DAX会将CALCULATE使用的语法糖形式，还原成完整的表筛选，对应的代码如下:

代码语言：javascript
复制
= CALCULATE( 
    MAX( Customer[YearlyIncome]), 
    FILTER(
        ALL(Customer[Education]), 
        Customer[Education]="High School"
    )
  )

ALL函数会创建对空行的依赖关系。一旦你了解了循环依赖的基本原理后，删除它们并不复杂。前面的例子可以改写成下面这样：

代码语言：javascript
复制
=CALCULATE( 
    MAX( Customer[YearlyIncome]), 
    FILTER(
        ALLNOBLANKROW(Customer[Education]), 
        Customer[Education]="High School"
    )
 )

使用ALLNOBLANKROW代替ALL，对Customer表可能新增的空行的依赖关系就消失了。

延伸阅读：

（1）规范化与非规范化

规范化这一术语用于描述以减少重复数据的方式存储的数据。假设有一个产品表具有一个唯一密钥值列（如产品密钥）和描述产品特征（包括产品名称、类别、颜色和尺寸）的其他列。当销售表仅存储密钥（如产品密钥）时，该表被视为是规范化的。在下图中，请注意，只有 ProductKey 列记录产品。

然而，如果除了密钥，销售表还存储了产品详细信息，则该表被视为是非规范化的。在下图中，请注意，ProductKey和其他产品相关列记录了产品。

参考资料：

[1] DAX权威指南(https://item.jd.com/13168782.html)

[2] DAX：关系概述（高阶）(https://www.cnblogs.com/ljhdo/p/5158596.html)

[3] 了解星型架构及其对 Power BI 的重要性(https://learn.microsoft.com/zh-cn/power-bi/guidance/star-schema)

#存储#bi#产品#规范化#函数