DAX计算列技术问题：如何在筛选表中返回上一行值？

问题描述 #

需要创建计算列，按User - Username分组，返回当前行同用户的上一行（按ID降序，即ID更小的最近一条记录）的StopTime，原DAX公式运行时Excel持续卡顿，疑似行引用导致计算效率低下。

原公式：

=
VAR RowID = Table1[ID]
VAR UserID = Table1[User - Username]
RETURN
CALCULATE(
    MAX(Table1[StopTime]),
    TOPN(
        1,
        FILTER(
            Table1,
            Table1[ID] < RowID
        ),
        Table1[ID],
        DESC
    ),
    Table1[User - Username] = UserID
)

卡顿原因分析 #

原公式中，TOPN的FILTER会先扫描全表筛选所有ID小于当前行的记录，再结合用户筛选条件缩小范围。每一行计算都要执行一次全表扫描，当数据量较大时，这种逐行全表遍历的操作会导致Excel资源占用过高，出现卡顿。

优化解决方案 #

通过分两步计算缩小筛选范围，先在同用户组内找到最大的前序ID，再根据该ID获取对应StopTime，大幅降低计算量：

=VAR CurrentID = Table1[ID]
VAR CurrentUser = Table1[User - Username]
-- 第一步：找同用户下比当前ID小的最大ID
VAR PreviousUserID = 
    CALCULATE(
        MAX(Table1[ID]),
        Table1[User - Username] = CurrentUser,
        Table1[ID] < CurrentID
    )
-- 第二步：根据找到的ID获取对应StopTime
RETURN
CALCULATE(
    MAX(Table1[StopTime]),
    Table1[User - Username] = CurrentUser,
    Table1[ID] = PreviousUserID
)

样本数据验证 #

使用以下样本数据，优化后的公式可返回期望的计算列结果：

内容的提问来源于stack exchange，提问作者Jack Kaplan