DAX表达式报错：SWITCH/TRUE逻辑中CALCULATE用于布尔表达式排查

问题场景 #

尝试编写DAX逻辑在损益表（P&L）中同时显示月度数据与年初至今（YTD）数据，月度计算逻辑正常，但添加YTD逻辑后出现报错：

'CALCULATE' has been used in a True/False expression on YTD SWITCH/TRUE Logic

原DAX代码如下：

Selected YTD Actuals = 
VAR CURRENTITEM = SELECTEDVALUE( 'P&L Template'[Items (Normalized)])
 return 
 SWITCH( TRUE(),
  CURRENTITEM = "Total Staff Costs", [YTD Total Staff Costs],
  CURRENTITEM = "Total Other Overheads", [Total Other Overheads YTD],
  CURRENTITEM = "Total Spend", [Total Spend YTD],
  CALCULATE(CALCULATE([YTD Cost], FILTER( 'Transaction Data', 'Transaction Data'[CATEGORY] = CURRENTITEM), CALCULATE ([Total Cost], FILTER('Transaction Data', DATESYTD (Dates[Date]))))))

错误原因 #

1. SWITCH分支结构错误：SWITCH(TRUE())的语法要求每一项是「布尔条件, 返回值」的配对。最后一行直接写CALCULATE(...)，DAX会把这个计算结果当成布尔条件判断，但CALCULATE返回的是数值而非布尔值，触发报错。
2. CALCULATE逻辑冗余：[YTD Cost]本身应该已经包含YTD时间筛选逻辑，无需嵌套CALCULATE([Total Cost], FILTER('Transaction Data', DATESYTD (Dates[Date])))，这会导致筛选上下文冲突。

解决方法 #

• 给默认分支添加TRUE()作为条件，明确这是所有匹配失败后的返回逻辑
• 简化CALCULATE表达式，保留必要的分类筛选即可（若[YTD Cost]未内置YTD逻辑，可直接用DATESYTD筛选日期表）

修正后的DAX代码 #

Selected YTD Actuals = 
VAR CURRENTITEM = SELECTEDVALUE( 'P&L Template'[Items (Normalized)])
RETURN 
 SWITCH( TRUE(),
  CURRENTITEM = "Total Staff Costs", [YTD Total Staff Costs],
  CURRENTITEM = "Total Other Overheads", [Total Other Overheads YTD],
  CURRENTITEM = "Total Spend", [Total Spend YTD],
  -- 默认分支：匹配其他所有分类
  TRUE(), CALCULATE([YTD Cost], 'Transaction Data'[CATEGORY] = CURRENTITEM)
 )

补充说明 #

如果[YTD Cost]未预先定义YTD逻辑，可直接在CALCULATE中添加时间筛选：

Selected YTD Actuals = 
VAR CURRENTITEM = SELECTEDVALUE( 'P&L Template'[Items (Normalized)])
RETURN 
 SWITCH( TRUE(),
  CURRENTITEM = "Total Staff Costs", [YTD Total Staff Costs],
  CURRENTITEM = "Total Other Overheads", [Total Other Overheads YTD],
  CURRENTITEM = "Total Spend", [Total Spend YTD],
  TRUE(), CALCULATE([Total Cost], 
                   'Transaction Data'[CATEGORY] = CURRENTITEM,
                   DATESYTD(Dates[Date])
                  )
 )

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