DAX查询报错：预期返回单值却被提供多值表的问题排查

报错根本原因 #

你遇到的"A table of multiple values was supplied where a single value was expected"错误，核心触发点有两个：

• 定义的DoNotWithtake变量通过VALUES('Målerpunkt'[Metering Point ID])返回的是包含多个计量点ID的单列表，后续在IN { DoNotWithtake }语法中错误用大括号包裹了这个表：DAX中{}是构造表的语法，把多值表塞进{}会生成嵌套表，和前面的'Målerpunkt'[Metering Point ID]单列做匹配时，既会出现类型不匹配，也会因为返回多值触发报错。
• 若你的报表上下文里Dato[Dato]处于多日期筛选范围，DATEADD(Dato[Dato],1,MONTH)会返回多日期表，部分聚合场景下也可能触发该报错。

修正后的度量值代码 #

Testmeasure =
VAR SAEndDatePlusOneMonth =
    FIRSTDATE ( DATEADD ( ALLSELECTED(Dato[Dato]), 1, MONTH ) )
VAR SAStartDatePlusTwoMonths =
    FIRSTDATE ( DATEADD ( ALLSELECTED(Dato[Dato]), 2, MONTH ) )
VAR CurrentMinDate = MIN ( Dato[Dato] )
VAR DoNotWithtake =
    CALCULATE (
        DISTINCT ( 'Målerpunkt'[Metering Point ID] ),
        FILTER (
            CALCULATETABLE ( VALUES ( FactSA ), ALL ( 'Dato' ) ),
            FactSA[SAStartDate] >= CurrentMinDate
                && FactSA[SAStartDate] < SAStartDatePlusTwoMonths
                && FactSA[SAEndDate] >= SAEndDatePlusOneMonth
        ),
        FILTER ( SA, NOT ( SA[SA status] IN { "Canceled", "Pending Start" } ) ),
        ALL ( Dato[Dato] )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Målerpunkt'[Metering Point ID] ),
        FILTER (
            CALCULATETABLE ( VALUES ( FactSA[SAEndDate] ), ALL ( 'Dato' ) ),
            FactSA[SAEndDate] >= CurrentMinDate
                && FactSA[SAEndDate] < SAEndDatePlusOneMonth
        ),
        FILTER ( SA, SA[SA status] IN { "Stopped", "Closed" } ),
        FILTER (
            'Målerpunkt',
            NOT ( 'Målerpunkt'[Metering Point ID] IN DoNotWithtake )
        ),
        ALL ( Dato[Dato] )
    )

优化说明 #

• 提前提取CurrentMinDate变量，避免多次嵌套调用CALCULATE(MIN(Dato[Dato]))带来的上下文混乱问题，同时提升度量值计算性能
• 日期函数改用ALLSELECTED(Dato[Dato])锁定当前上下文选中的日期范围，避免切片器、行上下文带来的日期多值异常
• 用DISTINCT替换VALUES，避免重复值对匹配逻辑的干扰，和后续的去重计数逻辑对齐

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