Power BI中求和与筛选函数组合使用失效问题排查

我有一张包含「Closed Day 1」到「Closed Day 10」共10列的表格，需要对前5天的数据求和，再按「Category」列筛选出值为"Servicing"的记录。仅做求和的度量值能正常运行，但加上筛选后就报错了。

正常运行的度量值 #

Comp Within Complete 5 days = 
SUM(Completed[Closed Day 1]) + 
SUM(Completed[Closed Day 2]) + 
SUM(Completed[Closed Day 3]) + 
SUM(Completed[Closed Day 4]) + 
SUM(Completed[Closed Day 5])

添加筛选后报错的度量值 #

Comp Within Complete 5 days = 
SUM(Completed[Closed Day 1]) + 
SUM(Completed[Closed Day 2]) + 
SUM(Completed[Closed Day 3]) + 
SUM(Completed[Closed Day 4]) + 
SUM(Completed[Closed Day 5])

Filter(Table, Table[Category] = "Servicing"))

报错截图 #

错误原因 #

你的写法违反了DAX语法规则：

• Filter是表函数，返回的是筛选后的表对象，不能直接跟在标量求和结果后拼接，两者数据类型不兼容。
• 要实现「带筛选条件的求和」，必须用CALCULATE函数包装求和逻辑，将筛选条件作为CALCULATE的参数传入。

正确的度量值写法 #

写法一：直接用CALCULATE包装原求和逻辑

Comp Within Complete 5 days = 
CALCULATE(
    SUM(Completed[Closed Day 1]) + 
    SUM(Completed[Closed Day 2]) + 
    SUM(Completed[Closed Day 3]) + 
    SUM(Completed[Closed Day 4]) + 
    SUM(Completed[Closed Day 5]),
    Completed[Category] = "Servicing"  -- 直接传入筛选条件，无需单独调用Filter
)

写法二：优化求和逻辑（减少重复SUM调用）

Comp Within Complete 5 days = 
CALCULATE(
    SUMX(
        Completed,
        Completed[Closed Day 1] + Completed[Closed Day 2] + Completed[Closed Day 3] + Completed[Closed Day 4] + Completed[Closed Day 5]
    ),
    Completed[Category] = "Servicing"
)

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