Power BI中基于StateAfter列计算Msg分组总时长的问询

需求说明 #

数据表包含Msg、时间戳（Timestamp）、StateAfter三列，其中StateAfter=1代表进入状态，StateAfter=0代表离开状态。需要计算每个Msg对应的总停留时长，且Msg的进出记录并非连续排列。

示例原始数据：

预期最终结果：

方法一：Power Query编辑器处理（推荐） #

通过Power Query对数据分组排序，匹配每条进入记录对应的离开记录，最终汇总时长：

1. 导入数据后进入Power Query编辑器，将以下M代码替换原有查询（注意替换你的数据源为实际表名）：

let
    源 = 你的数据源,
    // 按Msg分组，并对每组记录按时间戳升序排序
    分组并排序 = Table.Group(源, {"Msg"}, {{"记录", each Table.Sort(_,{{"时间戳", Order.Ascending}})}}),
    // 为每组记录匹配对应离开时间并计算单次时长
    添加时长计算 = Table.TransformColumns(分组并排序, {"记录", (tbl) => 
        let
            // 为进入记录标记对应的离开记录索引
            添加配对索引 = Table.AddColumn(tbl, "配对索引", each if [StateAfter] = 1 then List.PositionOf(tbl[StateAfter], 0, Occurrence.After, [时间戳]) else null),
            // 获取对应离开时间
            添加离开时间 = Table.AddColumn(添加配对索引, "离开时间", each if [StateAfter] = 1 then try tbl[时间戳]{[配对索引]} otherwise null else null),
            // 计算单次停留时长
            计算单次时长 = Table.AddColumn(添加离开时间, "单次时长", each if [离开时间] <> null then Duration.From([离开时间] - [时间戳]) else null)
        in
            计算单次时长
    }),
    // 展开分组后的记录
    展开记录 = Table.ExpandTableColumn(添加时长计算, "记录", {"时间戳", "StateAfter", "单次时长"}, {"时间戳", "StateAfter", "单次时长"}),
    // 按Msg汇总总时长，并格式化为hh:mm:ss
    汇总总时长 = Table.Group(展开记录, {"Msg"}, {{"总时长", each Duration.ToText(List.Sum(List.RemoveNulls(_[单次时长])), "hh:mm:ss"), type text}})
in
    汇总总时长

2. 执行查询后即可得到每个Msg的总停留时长。

方法二：DAX计算 #

通过创建计算列和度量值实现时长统计：

步骤1：创建计算列「最近进入时间」 #

匹配每条离开记录对应的最近未被使用的进入时间：

最近进入时间 = 
VAR 当前Msg = '表'[Msg]
VAR 当前时间 = '表'[时间戳]
RETURN
    IF(
        '表'[StateAfter] = 0,
        CALCULATE(
            MAX('表'[时间戳]),
            FILTER(
                ALL('表'),
                '表'[Msg] = 当前Msg &&
                '表'[StateAfter] = 1 &&
                '表'[时间戳] < 当前时间 &&
                // 确保该进入记录未被更早的离开记录匹配
                NOT(EXISTS(
                    FILTER('表', '表'[Msg] = 当前Msg && '表'[StateAfter] = 0 && '表'[时间戳] > '表'[时间戳] && '表'[时间戳] < 当前时间)
                ))
            )
        ),
        BLANK()
    )

步骤2：创建计算列「单次时长」 #

计算每条离开记录对应的单次停留时长：

单次时长 = 
IF(
    '表'[StateAfter] = 0,
    FORMAT('表'[时间戳] - '表'[最近进入时间], "hh:mm:ss"),
    BLANK()
)

步骤3：创建度量值「总时长」 #

汇总每个Msg的所有单次时长：

总时长 = 
VAR 有效时长列表 = 
    CALCULATETABLE(
        VALUES('表'[单次时长]),
        FILTER('表', '表'[单次时长] <> BLANK())
    )
RETURN
    FORMAT(
        SUMX(有效时长列表, TIMEVALUE([单次时长])),
        "hh:mm:ss"
    )

将Msg列和「总时长」度量值放入可视化组件即可得到预期结果。

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