基于时间筛选用DAX统计最新状态为Open的ID数量

原始数据 #

需求 #

统计最新状态为Open的ID数量：

• 不设置日期筛选时，结果应为3个ID；
• 筛选日期范围为12/01/2023至14/01/2023时，结果应为1个ID。

尝试的DAX度量 #

status = CALCULATE(
    VALUES('Table'[Status]),
    FILTER(ALL('Table'[Date]), 'Table'[Date] = MAX('Table'[Date]))
)

问题分析 #

原度量的问题在于使用ALL('Table'[Date])移除了日期筛选上下文，但没有按ID分组获取每个ID的最新日期，导致无法精准匹配每个ID的最新状态，进而无法正确统计数量。

解决方案 #

可以拆分步骤实现（更易理解），也可以合并为单个高效度量：

方式一：拆分步骤 #

1. 获取每个ID的最新日期：

最新日期 = MAX('Table'[Date])

2. 获取每个ID的最新状态：

最新状态 = CALCULATE(
    VALUES('Table'[Status]),
    'Table'[Date] = [最新日期]
)

3. 统计最新状态为Open的ID数量：

Open状态ID数量 = CALCULATE(
    DISTINCTCOUNT('Table'[ID]),
    FILTER(
        VALUES('Table'[ID]),
        [最新状态] = "Open"
    )
)

方式二：合并为单个度量 #

Open状态ID数量 = CALCULATE(
    DISTINCTCOUNT('Table'[ID]),
    FILTER(
        VALUES('Table'[ID]),
        VAR LatestDate = CALCULATE(MAX('Table'[Date]))
        RETURN CALCULATE(VALUES('Table'[Status]), 'Table'[Date] = LatestDate) = "Open"
    )
)

验证效果 #

• 无日期筛选时：ID1、ID2、ID3的最新状态均为Open，统计结果为3；
• 筛选12/01/2023至14/01/2023时：仅ID1的最新状态为Open，统计结果为1，完全符合需求。

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