Power BI通过DAX实现维度属性最新版本的模型过滤

问题背景 #

• 模型包含多张表，每个ID对应多条带valid_from（生效日期）和valid_to（失效日期）的记录
• 一张兼具事实表与维度表功能的核心表，与其他表配置了双向交叉筛选
• 存在未与任何表关联的独立日期维度表

核心需求 #

1. 选中日期范围时：计算各ID在所选日期最大值之前的最新有效记录的金额之和
2. 未选择日期时：计算各ID的**当前版本（最新有效）**金额之和
3. 需确保维度属性筛选正常生效（双向筛选的上下文传递不受影响）

现有方案问题分析 #

方案1问题 #

Amount Measure=
VAR _maxSelectedDate = MAX(Dates[Dates])
VAR _minSelectedDate = MIN(Dates[Dates])
VAR _maxValidFrom = 
    CALCULATE(
        MAX(fact[valid_from]),
        DATESBETWEEN(fact[valid_from], _minSelectedDate, _maxSelectedDate)
        || DATESBETWEEN(fact[valid_to], _minSelectedDate, _maxSelectedDate)
    )

RETURN
CALCULATE(
    SUM(fact[Amount]),
    fact[valid_from] = _maxValidFrom
)

• 能正常响应维度属性筛选，但_maxValidFrom是全局范围内的最大值，并非按ID分组计算，导致仅会选取所有ID中拥有同一最大valid_from的记录，结果错误。

方案2问题与失效原因 #

Amount Measure=
VAR _maxSelectedDate = MAX(Dates[Dates])
VAR _minSelectedDate = MIN(Dates[Dates])
VAR _maxValidFromPerID = 
    SUMMARIZE(
        FILTER(
            fact,
            DATESBETWEEN(fact[valid_from], _minSelectedDate, _maxSelectedDate)
            || DATESBETWEEN(fact[valid_to], _minSelectedDate, _maxSelectedDate)
        ),
        fact[ID],
        "maxValidFrom",
        MAX(fact[valid_from])
    )

RETURN
    CALCULATE(
        SUM(fact[Amount]),
        TREATAS(
            _maxValidFromPerID,
            fact[ID],
            fact[valid_from]
        )
    )

• 能按ID正确计算最新的valid_from，但双向筛选失效，维度属性筛选时会出现重复计算。
• 失效原因：SUMMARIZE生成的临时表仅包含ID和maxValidFrom，随后TREATAS强制将这个临时表作为筛选条件覆盖到fact表，切断了维度表与fact表之间的上下文传递路径。双向筛选需要依赖原始上下文的筛选关联，临时表的强制筛选会忽略当前维度属性的筛选上下文，导致维度筛选无法同步过滤到对应的最新记录，进而出现重复计算。

解决方案DAX代码 #

方案一：基于LASTNONBLANK的实现 #

Amount Measure = 
VAR _maxSelectedDate = 
    IF(ISFILTERED(Dates[Dates]), MAX(Dates[Dates]), TODAY())
VAR _currentFilterContext = ALLSELECTED(fact)
RETURN
SUMX(
    SUMMARIZE(_currentFilterContext, fact[ID]),
    VAR _latestValidAmount = 
        CALCULATE(
            LASTNONBLANK(fact[Amount], 1),
            FILTER(
                _currentFilterContext,
                fact[valid_from] <= _maxSelectedDate
                && (fact[valid_to] >= _maxSelectedDate || ISBLANK(fact[valid_to]))
            )
        )
    RETURN
    _latestValidAmount
)

方案二：基于TOPN的实现（更直观） #

Amount Measure = 
VAR _maxSelectedDate = 
    IF(ISFILTERED(Dates[Dates]), MAX(Dates[Dates]), TODAY())
RETURN
SUMX(
    VALUES(fact[ID]),
    VAR _latestValidRecord = 
        TOPN(
            1,
            FILTER(
                RELATEDTABLE(fact),
                fact[valid_from] <= _maxSelectedDate
                && (fact[valid_to] >= _maxSelectedDate || ISBLANK(fact[valid_to]))
            ),
            fact[valid_from], DESC
        )
    RETURN
    MAXX(_latestValidRecord, fact[Amount])
)

方案说明 #

1. 日期逻辑处理：通过ISFILTERED判断是否有日期筛选，有则取选中的最大日期，无则用TODAY()代表当前版本的有效记录。
2. 保留筛选上下文：
   • 方案一中的_currentFilterContext = ALLSELECTED(fact)保留了当前所有筛选（包括维度属性的筛选），确保双向筛选的上下文能正常传递。
   • 方案二中的RELATEDTABLE(fact)利用双向筛选的特性，自动带入当前维度筛选下的fact记录集合。
3. 按ID取最新记录：
   • 两种方案均通过SUMX按ID分组计算，对每个ID单独筛选出符合日期条件的最新有效记录，再提取金额汇总。
   • 有效记录判断逻辑：valid_from不晚于选中日期，且valid_to不早于选中日期（或valid_to为空，代表该记录当前仍有效）。

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