修正DAX公式：去重保留最新条目后正确求和租赁面积

问题背景 #

Power BI数据模型中有Lease Unit表，需按以下规则计算各Asset Reference的租赁面积总和：

• 筛选满足Commencement Date ≤ [As Of Date]且Expiration Date ≥ [As Of Date]，或Lease Status为"Holding"/"Month-to-Month"的行；
• 进一步筛选Termination Date为空或≥ [As Of Date]的行；
• 若存在重复Unit Reference，保留Commencement Date最新的行；
• 对筛选后的Leased Area求和。

当前DAX公式计算错误：当[As Of Date]为2022-12-31时，Asset Reference 100的正确结果应为62840，但公式返回69437。

修正后的DAX公式 #

Leased Area Total = 
VAR AsOfDate = [As Of Date]
-- 步骤1+2：合并基础筛选逻辑，确保条件同时生效
VAR FilteredLeases = 
    FILTER(
        'Lease Unit',
        (
            ('Lease Unit'[Commencement Date] <= AsOfDate && 'Lease Unit'[Expiration Date] >= AsOfDate)
            || 'Lease Unit'[Lease Status] IN {"Holding", "Month-to-Month"}
        )
        && (ISBLANK('Lease Unit'[Termination Date]) || 'Lease Unit'[Termination Date] >= AsOfDate)
    )
-- 步骤3：按Unit Reference提取最新生效记录
VAR LatestUnitRecords = 
    ADDCOLUMNS(
        SUMMARIZE(FilteredLeases, 'Lease Unit'[Unit Reference]),
        "@LatestCommencement", CALCULATE(MAX('Lease Unit'[Commencement Date]))
    )
VAR FinalValidLeases = 
    NATURALINNERJOIN(FilteredLeases, LatestUnitRecords)
-- 步骤4：按Asset Reference汇总租赁面积
RETURN
    SUMX(
        SUMMARIZE(FinalValidLeases, 'Lease Unit'[Asset Reference], 'Lease Unit'[Leased Area]),
        'Lease Unit'[Leased Area]
    )

修正逻辑说明 #

1. 条件优先级修正：原公式大概率存在逻辑运算符优先级错误，未将步骤1的两个条件（日期范围/租赁状态）分组，导致部分不符合终止日期要求的行被错误保留。修正后通过括号明确分组，确保步骤1的任一条件满足时，必须同时符合步骤2的终止日期要求。
2. 去重逻辑严谨化：通过SUMMARIZE+MAX精准获取每个Unit Reference的最新生效日期，再用NATURALINNERJOIN匹配筛选后的行，彻底避免同一单元多条记录重复计算面积的问题。
3. 求和逻辑优化：最后按Asset Reference分组求和，避免因同一资产下多条有效记录的无差别累加导致结果偏大。

执行效率分析 #

1. 筛选阶段：FILTER直接在原表上做基础筛选，若Lease Unit表已对Commencement Date、Expiration Date、Termination Date、Lease Status建立列索引，筛选效率会显著提升。
2. 去重阶段：仅针对筛选后的子集做聚合计算，数据量更小；NATURALINNERJOIN是DAX中高效的表连接方式，性能优于嵌套CALCULATE的迭代逻辑。
3. 求和阶段：SUMX配合SUMMARIZE分组，仅对必要的行进行迭代，避免了冗余计算。

若表数据量达到百万行级，可提前创建计算列标记有效租赁记录（将步骤1+2的筛选逻辑固化为计算列），进一步缩短查询响应时间。

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