Power BI自定义度量值在矩阵以Coverage为行标题时返回0的问题

问题背景 #

现有保险保单数据包含CLIENTID、Coverage、AGENCYAMOUNT、BILLINGMONTH字段，通过切片器选择本年日期范围（对应度量值Start_CurrentYear/End_CurrentYear），自动计算上年同期范围（Start_PriorYear/End_PriorYear）。需求是汇总上年已存在客户在本年新增险种的AGENCYAMOUNT总和——比如上年客户100只有401k险种，本年新增Casualty，需单独汇总该Casualty的金额。

当前度量值在矩阵行标题设为CLIENTID时计算正常，但设为Coverage时所有行返回0。问题根源是行上下文限制：原逻辑判断客户是否存在于上年时，仅校验当前行Coverage对应的上年记录，而非检查该客户在所有上年记录中是否存在。

修正后的度量值 #

Existing Client, New Coverage =
VAR PriorYear_Clients =
    // 提取上年所有存在的唯一CLIENTID，不受行上下文干扰
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(Query, Query[BILLINGMONTH] >= [Start_PriorYear] && Query[BILLINGMONTH] <= [End_PriorYear]),
            "CLIENTID", Query[CLIENTID]
        )
    )
VAR CurrentYear_ClientCoverage =
    // 本年所有CLIENTID+Coverage组合
    SUMMARIZE(
        FILTER(Query, Query[BILLINGMONTH] >= [Start_CurrentYear] && Query[BILLINGMONTH] <= [End_CurrentYear]),
        Query[CLIENTID],
        Query[Coverage]
    )
VAR PriorYear_ClientCoverage =
    // 上年所有CLIENTID+Coverage组合
    SUMMARIZE(
        FILTER(Query, Query[BILLINGMONTH] >= [Start_PriorYear] && Query[BILLINGMONTH] <= [End_PriorYear]),
        Query[CLIENTID],
        Query[Coverage]
    )
VAR CurrentOnly_Coverage =
    // 本年有、上年无的险种组合
    EXCEPT(CurrentYear_ClientCoverage, PriorYear_ClientCoverage)
VAR Target_Combination =
    // 筛选出上年已存在客户的新增险种
    FILTER(CurrentOnly_Coverage, [CLIENTID] IN PriorYear_Clients)
RETURN
COALESCE(
    CALCULATE(
        SUM(Query[AGENCYAMOUNT]),
        FILTER(Query, Query[BILLINGMONTH] >= [Start_CurrentYear] && Query[BILLINGMONTH] <= [End_CurrentYear]),
        TREATAS(Target_Combination, Query[CLIENTID], Query[Coverage])
    ),
    0
)

关键修改说明 #

1. 拆分上年数据逻辑：新增PriorYear_Clients变量单独提取上年所有存在的客户ID，彻底脱离Coverage的行上下文限制，确保判断客户是否存在时，检查的是该客户在上年的所有记录，而非仅当前行险种对应的记录。
2. 分离险种对比与客户存在性判断：用PriorYear_ClientCoverage做险种组合的对比，用PriorYear_Clients做客户存在性校验，两者各司其职，避免行上下文干扰计算结果。

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