PowerBI如何使用DAX计算匹配往来消息的响应时间

匹配规则 #

满足以下全部条件的两条记录判定为「原接收消息-回复消息」配对：

• 回复记录的References字段包含原消息的MessageId
• 回复记录的Sender等于原消息的接收对象（即原消息DisplayTo字段值）
• 回复记录的DisplayTo字段包含原消息的Sender
  响应时长 = 回复消息的DateTimeSent - 匹配到的原消息的DateTimeReceived

示例数据集 #

测试表名统一命名为MessageLog，数据如下：

实现步骤 #

1. 先将DateTimeSent、DateTimeReceived列的数据类型修改为日期/时间，dd.MM.yyyy hh.mm.ss格式可被Power BI/SSAS Tabular自动识别，识别异常时手动指定日期解析格式即可。
2. 在MessageLog表中新建计算列，输入以下DAX代码：

响应时长（秒） = 
VAR CurrRef = 'MessageLog'[References]
VAR CurrSender = 'MessageLog'[Sender]
VAR CurrDisplayTo = 'MessageLog'[DisplayTo]
VAR CurrSendTime = 'MessageLog'[DateTimeSent]
// 匹配符合规则的原消息
VAR MatchOriginalMsg =
    FILTER(
        'MessageLog',
        SEARCH('MessageLog'[MessageId], CurrRef, 1, 0) > 0
        && 'MessageLog'[DisplayTo] = CurrSender
        && SEARCH('MessageLog'[Sender], CurrDisplayTo, 1, 0) > 0
    )
VAR OriginalRecvTime = MAXX(MatchOriginalMsg, 'MessageLog'[DateTimeReceived])
RETURN
IF(NOT ISBLANK(CurrRef), DATEDIFF(OriginalRecvTime, CurrSendTime, SECOND), BLANK())

代码说明 #

• 用SEARCH函数实现模糊匹配，无需提前拆分逗号分隔的多值字段，适配DisplayTo包含多个接收对象、References关联多个原消息的场景
• 时长单位默认取秒，可将DATEDIFF函数最后一个参数替换为MINUTE/HOUR/DAY，直接得到对应单位的响应时长
• 对References为空的原始接收消息返回空值，不做无效计算
• 极端场景下一条回复匹配到多条原消息时，取最晚的原消息接收时间作为计算基准，避免时长计算为负
• 针对示例中MessageId为AS8PR01M的原消息，匹配到的回复是DU2PR01M，计算得到响应时长为176924秒，和场景给出的时间差完全一致。

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