DAX代码报错：表达式引用多列无法转成标量求解决

我编写DAX代码，目的是计算podcount表中SMALL、MEDIUM、LARGE、MATURED UNRIPED、MATURED RIPED列的平均值，并基于日期生成指定周期预测，但代码触发报错："The Expression Refers to Multiple Columns. Multiple Columns Cannot Be Converted to a Scale"（中文：表达式引用了多列，多列无法转换为标量值）。先后尝试两段代码均未解决，请求协助排查。

第一段代码 #

PredictedAverages =
VAR Small_Maturity = 3 * 30,  // Assuming 1 month = 30 days
    Medium_Maturity = 2.5 * 30,
    Large_Maturity = 1.5 * 30,
    Matured_Unriped_Maturity = 14,
    Matured_Riped_Maturity = 0  // Assuming 'Now' means no additional maturity time
RETURN
SUMMARIZE(
    'podcount',
    'podcount'[COMMUNITY],
    'podcount'[DATE],
    "Total_Average_SMALL", SUMX(FILTER('podcount', 'podcount'[SMALL] <> BLANK()), 'podcount'[SMALL] / Small_Maturity),
    "Total_Average_MEDIUM", SUMX(FILTER('podcount', 'podcount'[MEDIUM] <> BLANK()), 'podcount'[MEDIUM] / Medium_Maturity),
    "Total_Average_LARGE", SUMX(FILTER('podcount', 'podcount'[LARGE] <> BLANK()), 'podcount'[LARGE] / Large_Maturity),
    "Total_Average_MATURED_UNRIPED", SUMX(FILTER('podcount', 'podcount'[MATURED UNRIPED] <> BLANK()), 'podcount'[MATURED UNRIPED] / Matured_Unriped_Maturity),
    "Total_Average_MATURED_RIPED", SUMX(FILTER('podcount', 'podcount'[MATURED RIPED] <> BLANK()), 'podcount'[MATURED RIPED] / Matured_Riped_Maturity)
)

第二段代码 #

PredictedAverages =
VAR Small_Maturity = 3 * 30  // Assuming 1 month = 30 days
VAR Medium_Maturity = 2.5 * 30
VAR Large_Maturity = 1.5 * 30
VAR Matured_Unriped_Maturity = 14
VAR Matured_Riped_Maturity = 0  // Assuming 'Now' means no additional maturity time

// Calculate averages
VAR AveragesTable =
    ADDCOLUMNS(
        'podcount',
        "Average_SMALL", 'podcount'[SMALL] / Small_Maturity,
        "Average_MEDIUM", 'podcount'[MEDIUM] / Medium_Maturity,
        "Average_LARGE", 'podcount'[LARGE] / Large_Maturity,
        "Average_MATURED_UNRIPED", 'podcount'[MATURED UNRIPED] / Matured_Unriped_Maturity,
        "Average_MATURED_RIPED", 'podcount'[MATURED RIPED] / Matured_Riped_Maturity
    )

// Summarize the averages
RETURN
SUMMARIZE(
    AveragesTable,
    'podcount'[COMMUNITY],
    'podcount'[NAME],
    "Total_Average_SMALL", SUM('AveragesTable'[Average_SMALL]),
    "Total_Average_MEDIUM", SUM('AveragesTable'[Average_MEDIUM]),
    "Total_Average_LARGE", SUM('AveragesTable'[Average_LARGE]),
    "Total_Average_MATURED_UNRIPED", SUM('AveragesTable'[Average_MATURED_UNRIPED]),
    "Total_Average_MATURED_RIPED", SUM('AveragesTable'[Average_MATURED_RIPED])
)

问题分析 #

第一段代码错误点 #

1. VAR语法错误：多个变量定义不能用逗号分隔，每个变量必须单独使用VAR声明。
2. 筛选上下文冲突：在SUMMARIZE中使用SUMX(FILTER('podcount', ...))时，直接过滤整个podcount表，没有遵循当前分组（COMMUNITY+DATE）的上下文，导致返回全表计算结果，与分组逻辑冲突，触发多列无法转换为标量的错误。
3. 除零风险：Matured_Riped_Maturity设为0，会导致除法运算报错。

第二段代码错误点 #

1. 临时表列引用错误：SUMMARIZE中引用临时表AveragesTable的列时，不需要加单引号，直接写[Average_SMALL]即可（单引号仅用于模型中的物理表）。
2. 除零风险未解决：Matured_Riped_Maturity为0的问题依然存在。
3. 分组维度不一致：第一段按COMMUNITY+DATE分组，第二段改为COMMUNITY+NAME，若业务需求是按日期生成预测，分组维度需对齐。

修正后的代码方案 #

方案一：使用CURRENTGROUP()匹配分组上下文 #

PredictedAverages =
VAR Small_Maturity = 3 * 30  // 假设1个月=30天
VAR Medium_Maturity = 2.5 * 30
VAR Large_Maturity = 1.5 * 30
VAR Matured_Unriped_Maturity = 14
VAR Matured_Riped_Maturity = 1  // 避免除零，若业务上成熟已熟无需周期，可调整为返回原值
RETURN
SUMMARIZE(
    'podcount',
    'podcount'[COMMUNITY],
    'podcount'[DATE],
    "Total_Average_SMALL", 
        SUMX(
            FILTER(
                CURRENTGROUP(),  // 仅筛选当前分组内的行
                NOT(ISBLANK('podcount'[SMALL]))
            ),
            'podcount'[SMALL] / Small_Maturity
        ),
    "Total_Average_MEDIUM", 
        SUMX(
            FILTER(
                CURRENTGROUP(),
                NOT(ISBLANK('podcount'[MEDIUM]))
            ),
            'podcount'[MEDIUM] / Medium_Maturity
        ),
    "Total_Average_LARGE", 
        SUMX(
            FILTER(
                CURRENTGROUP(),
                NOT(ISBLANK('podcount'[LARGE]))
            ),
            'podcount'[LARGE] / Large_Maturity
        ),
    "Total_Average_MATURED_UNRIPED", 
        SUMX(
            FILTER(
                CURRENTGROUP(),
                NOT(ISBLANK('podcount'[MATURED UNRIPED]))
            ),
            'podcount'[MATURED UNRIPED] / Matured_Unriped_Maturity
        ),
    "Total_Average_MATURED_RIPED", 
        SUMX(
            FILTER(
                CURRENTGROUP(),
                NOT(ISBLANK('podcount'[MATURED RIPED]))
            ),
            // 若成熟已熟无需周期，可替换为：IF(Matured_Riped_Maturity=0, 'podcount'[MATURED RIPED], 'podcount'[MATURED RIPED]/Matured_Riped_Maturity)
            'podcount'[MATURED RIPED] / Matured_Riped_Maturity
        )
)

方案二：用EARLIER()匹配分组维度 #

PredictedAverages =
VAR Small_Maturity = 3 * 30
VAR Medium_Maturity = 2.5 * 30
VAR Large_Maturity = 1.5 * 30
VAR Matured_Unriped_Maturity = 14
VAR Matured_Riped_Maturity = 1
RETURN
ADDCOLUMNS(
    // 先按目标维度分组
    SUMMARIZE('podcount', 'podcount'[COMMUNITY], 'podcount'[DATE]),
    "Total_Average_SMALL", 
        SUMX(
            FILTER(
                'podcount',
                'podcount'[COMMUNITY] = EARLIER('podcount'[COMMUNITY]) &&
                'podcount'[DATE] = EARLIER('podcount'[DATE]) &&
                NOT(ISBLANK('podcount'[SMALL]))
            ),
            'podcount'[SMALL] / Small_Maturity
        ),
    "Total_Average_MEDIUM", 
        SUMX(
            FILTER(
                'podcount',
                'podcount'[COMMUNITY] = EARLIER('podcount'[COMMUNITY]) &&
                'podcount'[DATE] = EARLIER('podcount'[DATE]) &&
                NOT(ISBLANK('podcount'[MEDIUM]))
            ),
            'podcount'[MEDIUM] / Medium_Maturity
        ),
    "Total_Average_LARGE", 
        SUMX(
            FILTER(
                'podcount',
                'podcount'[COMMUNITY] = EARLIER('podcount'[COMMUNITY]) &&
                'podcount'[DATE] = EARLIER('podcount'[DATE]) &&
                NOT(ISBLANK('podcount'[LARGE]))
            ),
            'podcount'[LARGE] / Large_Maturity
        ),
    "Total_Average_MATURED_UNRIPED", 
        SUMX(
            FILTER(
                'podcount',
                'podcount'[COMMUNITY] = EARLIER('podcount'[COMMUNITY]) &&
                'podcount'[DATE] = EARLIER('podcount'[DATE]) &&
                NOT(ISBLANK('podcount'[MATURED UNRIPED]))
            ),
            'podcount'[MATURED UNRIPED] / Matured_Unriped_Maturity
        ),
    "Total_Average_MATURED_RIPED", 
        SUMX(
            FILTER(
                'podcount',
                'podcount'[COMMUNITY] = EARLIER('podcount'[COMMUNITY]) &&
                'podcount'[DATE] = EARLIER('podcount'[DATE]) &&
                NOT(ISBLANK('podcount'[MATURED RIPED]))
            ),
            'podcount'[MATURED RIPED] / Matured_Riped_Maturity
        )
)

修正说明 #

• 修复VAR语法：每个变量单独声明，去掉逗号。
• 对齐分组上下文：使用CURRENTGROUP()或EARLIER()确保SUMX仅计算当前分组内的行，避免多列冲突。
• 解决除零问题：将Matured_Riped_Maturity设为1，或根据业务逻辑调整为直接返回原值。
• 优化空白判断：用NOT(ISBLANK())替代<> BLANK()，DAX中判断空白更严谨。

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