如何编写DAX度量值按服务类别统计未续约流失家庭数？

需求明确 #

• 统计目标：按服务类别统计因未续约流失的家庭数量
• 流失家庭判定规则：
  1. 无任何有效服务期（未续约，即所有服务期均已到期）
  2. 同时满足以下任一条件：
     • Household表StateCodeName字段值为Inactive
     • Household表ServiceCategory字段值为D（代表仅提供合同外服务）
• 数据表结构：
  • Household表：包含HouseholdID、StateCodeName、ServiceCategory字段
  • Service Period表：包含HouseholdID、Startdate、Enddate字段
  • 两表通过HouseholdID建立一对多关联
• 验证示例：2024年2月7日时，Taylor Swift（状态Inactive+未续约）、Lewis Capaldi（服务类别D+未续约）需被统计，合计2个流失家庭

原代码问题分析 #

原度量值仅筛选了Service Period表中2023-2024年已过期的服务期记录，未判断家庭是否完全无有效服务期，也未关联Household表的状态/服务类别条件，导致逻辑缺失，返回结果异常。

原代码：

Households Lost_ = 
VAR CurrentYear = YEAR(TODAY())
VAR EndOfYear_ = DATE(CurrentYear, 12, 31)
RETURN
    CALCULATE(
        COUNTROWS(SUMMARIZE('Merged - Service Period', 'Merged - Service Period'[finpal_householdid])),
        FILTER(
            'Merged - Service Period',
            'Merged - Service Period'[finpal_enddate] <= EndOfYear_ &&
            'Merged - Service Period'[finpal_enddate] > date(2022, 12, 31) &&
            'Merged - Service Period'[finpal_enddate] < TODAY()
        )
    )

正确DAX度量值 #

流失家庭数量 = 
VAR 当前日期 = TODAY()
-- 筛选所有无有效服务期的家庭（所有服务期均已到期，无续约）
VAR 无有效服务期的家庭 = 
    EXCEPT(
        VALUES(Household[HouseholdID]),
        CALCULATETABLE(
            VALUES('Service Period'[HouseholdID]),
            'Service Period'[Enddate] >= 当前日期
        )
    )
-- 筛选符合流失判定的家庭
VAR 符合流失条件的家庭 = 
    CALCULATETABLE(
        VALUES(Household[HouseholdID]),
        (Household[StateCodeName] = "Inactive" || Household[ServiceCategory] = "D"),
        Household[HouseholdID] IN 无有效服务期的家庭
    )
RETURN
    COUNTROWS(符合流失条件的家庭)

代码逻辑说明 #

1. 无有效服务期的家庭：通过EXCEPT函数排除存在未到期服务期的家庭，确保剩余家庭完全没有续约（所有服务都已结束）。
2. 符合流失条件的家庭：在无有效服务的家庭中，筛选出满足Household表状态或服务类别要求的记录。
3. 返回统计数量：若所有客户均已续约，无有效服务期的家庭为空表，最终返回0，符合预期。

按服务类别细分统计 #

若需要按服务类别拆分统计结果，只需在可视化组件中添加Household表的ServiceCategory字段作为维度，配合上述度量值即可自动生成分类统计数据。

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