Power BI度量值筛选后获取列值的报错问题求助

错误原因 #

你写的DAX中，IF函数的第一个参数要求是单个布尔值（TRUE/FALSE），但FILTER返回的是完整的表对象，无法直接转换成标量逻辑值，这就是报错The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.的根源。

正确DAX写法 #

根据你的需求（排除指定状态的行，返回对应TicketAge值），分两种场景给出解决方案：

场景1：添加计算列（逐行判断） #

如果是在Database表中新增计算列，直接针对当前行的State做判断即可：

MS AgeTicket = 
IF(
    NOT Database[State] IN {"Closed Complete", "Cancelled", "Closed", "Closed Cancelled", "Canceled", "Closed Incomplete", "Closed Skipped", "Resolved"},
    Database[CLMN TicketAge],
    0
)

针对你的样本数据，计算结果为：

• 第一行（Closed Complete）：返回0
• 第二行（Open）：返回20
• 第三行（In Progress）：返回20

场景2：创建度量值（上下文聚合） #

如果是用于可视化的度量值（比如卡片图、矩阵聚合），需要结合CALCULATE来应用筛选逻辑：

示例1：求和符合条件的TicketAge

MS AgeTicket Sum = 
VAR ExcludedStates = {"Closed Complete", "Cancelled", "Closed", "Closed Cancelled", "Canceled", "Closed Incomplete", "Closed Skipped", "Resolved"}
RETURN
CALCULATE(
    SUM(Database[CLMN TicketAge]),
    NOT Database[State] IN ExcludedStates
)

示例2：返回单个选中值（上下文仅一行时）

如果需要用SELECTEDVALUE返回单个值，先过滤表再判断行数：

MS AgeTicket Selected = 
VAR ExcludedStates = {"Closed Complete", "Cancelled", "Closed", "Closed Cancelled", "Canceled", "Closed Incomplete", "Closed Skipped", "Resolved"}
VAR FilteredRows = FILTER(Database, NOT Database[State] IN ExcludedStates)
RETURN
IF(COUNTROWS(FilteredRows) = 1, SELECTEDVALUE(FilteredRows[CLMN TicketAge]), 0)

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