DAX度量值总计正常但表格明细层级失效问题排查

阿华AIGC实验室

2026-6-12

问题场景

在Power BI表格视觉对象中计算项目预算，要求不受Period Year筛选器影响，完整显示总预算。当前Project Budget度量值总计显示正常，但明细层级未达预期：当Period Year切片器选中所有值时，明细行预算值正确；应用任意年份筛选后，结果出现偏差。由于表格需同时展示实际值和预测值，无法直接移除交互。当前使用的DAX代码如下：

Project Budget = 
VAR SelectedConsolidation = SELECTEDVALUE('Consolidation_flag'[Consolidation view - Copy])
RETURN
SWITCH(
    TRUE(),
    SelectedConsolidation = "Switch to ARE Consolidated view",
        COALESCE(
            CALCULATE(
                SUM('Final data'[Net Cost EUR]),
                'Final data'[Bulk graphic] = "Project",
                'Final data'[Type] = "Budget",
                'Final data'[Data Source] = "Provider",
                'Final data'[Provider Type] IN {"I", "E", "S"},
                NOT 'Final data'[Supplier] IN {"SMO_088888"},
                NOT 'Final data'[Operation Type] IN {"RDCB", "9"},
                REMOVEFILTERS('Final data'[Period Year]) 
            ),
            0
        ),
    SelectedConsolidation = "Switch to World Consolidated view",
        COALESCE(
            CALCULATE(
                SUM('Final data'[M Net Cost EUR unconsolidated]),
                'Final data'[Bulk graphic] = "Project",
                'Final data'[Type] = "Budget",
                'Final data'[Data Source] IN {"Provider", "Receiver"},
                NOT 'Final data'[Supplier] IN {"SMO_088888"},
                NOT 'Final data'[Operation Type] IN {"RDCB", "9"},
                REMOVEFILTERS('Final data'[Period Year]) 
            ),
            0
        ),
    0  // Default if consolidation flag is blank or unmatched
)

问题根源

若Period Year切片器基于独立日期表而非事实表Final data的列，仅移除事实表Period Year的筛选无法清除切片器的年份过滤逻辑；
CALCULATE中的硬编码筛选条件未保留行上下文（如项目、供应商等维度）的筛选，导致明细行计算的是全局预算而非当前维度的总预算。

修正方案

情况1：Period Year切片器基于事实表`Final data`的`Period Year`列

使用KEEPFILTERS保留行上下文的维度筛选，仅清除年份列的筛选：

Project Budget = 
VAR SelectedConsolidation = SELECTEDVALUE('Consolidation_flag'[Consolidation view - Copy])
RETURN
SWITCH(
    TRUE(),
    SelectedConsolidation = "Switch to ARE Consolidated view",
        COALESCE(
            CALCULATE(
                SUM('Final data'[Net Cost EUR]),
                KEEPFILTERS(
                    'Final data'[Bulk graphic] = "Project" &&
                    'Final data'[Type] = "Budget" &&
                    'Final data'[Data Source] = "Provider" &&
                    'Final data'[Provider Type] IN {"I", "E", "S"} &&
                    NOT 'Final data'[Supplier] IN {"SMO_088888"} &&
                    NOT 'Final data'[Operation Type] IN {"RDCB", "9"}
                ),
                REMOVEFILTERS('Final data'[Period Year])
            ),
            0
        ),
    SelectedConsolidation = "Switch to World Consolidated view",
        COALESCE(
            CALCULATE(
                SUM('Final data'[M Net Cost EUR unconsolidated]),
                KEEPFILTERS(
                    'Final data'[Bulk graphic] = "Project" &&
                    'Final data'[Type] = "Budget" &&
                    'Final data'[Data Source] IN {"Provider", "Receiver"} &&
                    NOT 'Final data'[Supplier] IN {"SMO_088888"} &&
                    NOT 'Final data'[Operation Type] IN {"RDCB", "9"}
                ),
                REMOVEFILTERS('Final data'[Period Year])
            ),
            0
        ),
    0 // Default if consolidation flag is blank or unmatched
)

情况2：Period Year切片器基于独立日期表（如`Date`表）

若存在单独的日期表且切片器使用该表的Year列，需修改REMOVEFILTERS的对象为日期表的年份列：

Project Budget = 
VAR SelectedConsolidation = SELECTEDVALUE('Consolidation_flag'[Consolidation view - Copy])
RETURN
SWITCH(
    TRUE(),
    SelectedConsolidation = "Switch to ARE Consolidated view",
        COALESCE(
            CALCULATE(
                SUM('Final data'[Net Cost EUR]),
                KEEPFILTERS(
                    'Final data'[Bulk graphic] = "Project" &&
                    'Final data'[Type] = "Budget" &&
                    'Final data'[Data Source] = "Provider" &&
                    'Final data'[Provider Type] IN {"I", "E", "S"} &&
                    NOT 'Final data'[Supplier] IN {"SMO_088888"} &&
                    NOT 'Final data'[Operation Type] IN {"RDCB", "9"}
                ),
                REMOVEFILTERS('Date'[Year]) // 替换为你的日期表年份列
            ),
            0
        ),
    SelectedConsolidation = "Switch to World Consolidated view",
        COALESCE(
            CALCULATE(
                SUM('Final data'[M Net Cost EUR unconsolidated]),
                KEEPFILTERS(
                    'Final data'[Bulk graphic] = "Project" &&
                    'Final data'[Type] = "Budget" &&
                    'Final data'[Data Source] IN {"Provider", "Receiver"} &&
                    NOT 'Final data'[Supplier] IN {"SMO_088888"} &&
                    NOT 'Final data'[Operation Type] IN {"RDCB", "9"}
                ),
                REMOVEFILTERS('Date'[Year]) // 替换为你的日期表年份列
            ),
            0
        ),
    0 // Default if consolidation flag is blank or unmatched
)

关键说明