使用DAX的SUMX函数计算非累计变化值的问题排查

问题背景 #

数据集包含多ID、多时段的累计数据（Calls Handled和Total Handle Time为累计值），示例如下：

创建计算列时，能正确计算每行的非累计值，求和后结果符合预期（8:00=1、9:00=1、10:00=2）：

New Calls Handled:=
  Data[Calls Handled] - 
  LOOKUPVALUE(
    Data[Calls Handled],
    Data[Interval],
    CALCULATE( 
      MAX( Data[Interval] ),
      FILTER( Data, Data[Interval] < EARLIER( Data[Interval] ) )
    ),
    Data[ID], Data[ID] 
  )

但改为SUMX度量值后，结果变为累计值（8:00=1、9:00=2、10:00=4），不符合预期：

New Calls Handled_2:=
  SUMX(
    Data,
    Data[Calls Handled] - 
    LOOKUPVALUE(
      Data[Calls Handled],
      Data[Interval],
      CALCULATE( 
        MAX(Data[Interval]),
        FILTER( Data, Data[Interval] < EARLIER( Data[Interval] ) )
      ),
      Data[ID], Data[ID]
    )
  )

错误原因 #

1. 数据访问范围差异：

   • 计算列是在数据加载阶段逐行计算的，此时没有任何筛选限制，FILTER(Data, ...)能访问整个表的所有行，可正确找到当前ID对应的上一个时段的累计值，算出单时段的增量。
   • SUMX度量值是跟着当前筛选条件走的——比如你筛选9:00时，Data表仅包含该行数据。这时FILTER(Data, ...)只能在已筛选的行里找比9:00早的时段，根本找不到，LOOKUPVALUE返回空白，差值就等于当前行的累计值，最终SUMX求和结果就是累计值，而非预期的增量。

2. 行上下文作用域限制：
   SUMX内部的EARLIER(Data[Interval])指向当前遍历行的时段，但外层筛选会限制CALCULATE的数据源范围，导致它无法获取到当前ID的所有时段数据，自然找不到上一个时段的累计值。

解决方法 #

修改SUMX中的CALCULATE部分，使用ALLEXCEPT保留当前ID的筛选，同时移除Interval的筛选，确保能访问到当前ID的所有时段数据：

New Calls Handled_2:=
SUMX(
    Data,
    Data[Calls Handled] - 
    LOOKUPVALUE(
        Data[Calls Handled],
        Data[Interval],
        CALCULATE( 
            MAX(Data[Interval]),
            ALLEXCEPT(Data, Data[ID]), // 保留当前ID的所有行，移除其他筛选限制
            Data[Interval] < EARLIER(Data[Interval])
        ),
        Data[ID], Data[ID]
    )
)

补充优化 #

如果需要支持用户自定义时段范围的筛选（比如只计算已选择时段内的增量），可以替换为ALLSELECTED结合ID筛选，确保仅在用户选择的时段内查找上一个时段：

New Calls Handled_3:=
SUMX(
    Data,
    Data[Calls Handled] - 
    LOOKUPVALUE(
        Data[Calls Handled],
        Data[Interval],
        CALCULATE( 
            MAX(Data[Interval]),
            ALLSELECTED(Data[Interval]),
            Data[ID] = EARLIER(Data[ID]),
            Data[Interval] < EARLIER(Data[Interval])
        ),
        Data[ID], Data[ID]
    )
)

内容的提问来源于stack exchange，提问作者Juan Sebastián Vence