DAX中使用LOOKUPVALUE遇错，求解决及替代方案

问题背景 #

此前通过日期ID截取自然月的方式统计当月创建并执行的作业数量，公式如下：

CALCULATE('ia FactConv'[Sum of Jobs], LEFT('ia FactConv'[CreationDateId],6) = LEFT('ia FactConv'[ExecutedDateId],6))

现在需要切换为按自定义账期（BookMonth，如29日至次月28日等）统计，尝试使用LOOKUPVALUE关联日期维度表的账期字段，但报错“无法确定FactConv中CreationDateId的单一值”。

1. 为何无法按预期使用LOOKUPVALUE？ #

LOOKUPVALUE是逐行计算函数，要求传入的search_value必须是单一值；但度量值运行在聚合上下文中，直接引用'ia FactConv'[CreationDateId]这类列时，上下文可能包含多个值（比如筛选了多个日期、分组统计场景），LOOKUPVALUE无法处理多值输入，因此触发报错。

之前的自然月公式能生效，是因为LEFT函数在CALCULATE的筛选器中是行上下文内的逐行比较，而将LOOKUPVALUE放在VAR中调用时，没有逐行的执行环境，直接在聚合上下文里引用多值列就会出错。

2. 如何调整公式使其生效？ #

提供两种可行方案：

方案一：用SUMX逐行遍历判断（无需模型关系）

通过SUMX遍历事实表的每一行，逐行获取账期并判断是否相等，符合条件则累加作业数：

SUMX(
    'ia FactConv',
    VAR CreationBookMonth = LOOKUPVALUE(
        'ia DimCreaDate'[BookMonthNumber],
        'ia DimCreaDate'[DateId],
        'ia FactConv'[CreationDateId]
    )
    VAR ExecutionBookMonth = LOOKUPVALUE(
        'ia DimExecDate'[BookMonthNumber],
        'ia DimExecDate'[DateId],
        'ia FactConv'[ExecutedDateId]
    )
    RETURN IF(CreationBookMonth = ExecutionBookMonth, 'ia FactConv'[Jobs], 0)
)

注：如果[Sum of Jobs]是基于[Jobs]列的聚合度量值，这里直接用基础列[Jobs]即可。

方案二：利用模型关系（推荐，性能更优）

先建立维度表与事实表的关系：

• 将'ia DimCreaDate'[DateId]与'ia FactConv'[CreationDateId]建立一对多关系
• 将'ia DimExecDate'[DateId]与'ia FactConv'[ExecutedDateId]建立一对多关系

然后用RELATED函数关联账期字段，在CALCULATE中筛选相等条件：

CALCULATE(
    'ia FactConv'[Sum of Jobs],
    RELATED('ia DimCreaDate'[BookMonthNumber]) = RELATED('ia DimExecDate'[BookMonthNumber])
)

3. 是否有更简便的实现方式？ #

最简便高效的方式是添加计算列+建立模型关系：

1. 在'ia FactConv'中添加计算列，提前判断每行的创建与执行账期是否一致：

SameBookMonth = 
VAR CreationBM = RELATED('ia DimCreaDate'[BookMonthNumber])
VAR ExecutionBM = RELATED('ia DimExecDate'[BookMonthNumber])
RETURN CreationBM = ExecutionBM

2. 直接用该计算列作为筛选条件编写度量值：

CALCULATE('ia FactConv'[Sum of Jobs], 'ia FactConv'[SameBookMonth] = TRUE())

这种方式性能更好，计算列在数据刷新时一次性计算完成，避免了度量值实时逐行计算的开销，尤其适合大数据量场景。

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