DAX函数返回空白问题排查：变量与硬编码年份匹配异常

我编写了一段DAX代码，用于检测是否对'Campaign Overview'[campaign]进行了筛选，若已筛选则获取上一活动年份，返回该年份活动的总销售额，但函数仅返回空白。

代码如下：

testing = 
VAR extractCampaignYear = IF (ISFILTERED('Campaign Overview'[campaign]),MIN('Campaign Overview'[previousExtractedYear]),0)

RETURN

SUMX(
FILTER(
    SUMMARIZE (
    'Campaign Overview',
    'Campaign Overview'[campaign],
    "extractedCampaign",TRIM(MIN('Campaign Overview'[extractedCampaignName])),
    "campaignYear", MIN('Campaign Overview'[extractedYearFromCampaign]),
    "Sales", SUM ( 'Campaign Overview'[sales] )
),
[campaignYear] = extractCampaignYear), [Sales])

但当我将条件中的extractCampaignYear替换为硬编码值（如[campaignYear] = 2022）时，函数可正常返回结果。请问为何campaignYear与extractCampaignYear无法匹配？

使用变量时的结果：

硬编码年份时的结果：

原因分析 #

1. 上下文不匹配：变量extractCampaignYear是在外部筛选上下文（报表当前筛选器）下计算的，而SUMMARIZE生成的表是按campaign分组的内部行上下文。如果筛选多个campaign，MIN('Campaign Overview'[previousExtractedYear])会返回所有筛选结果中的最小值，和分组后的campaignYear无法对应；即使单campaign筛选，MIN也不如SELECTEDVALUE精准锁定当前筛选值。
2. 数据类型不一致：previousExtractedYear和extractedYearFromCampaign可能一个是文本、一个是数值，导致数值2022和文本"2022"无法匹配。
3. 全局变量的局限性：变量是一次性计算的固定值，无法跟随SUMMARIZE的分组行上下文动态调整，当分组后的campaignYear和全局变量值不重合时就返回空白。

解决方案 #

方案1：针对单campaign筛选场景修正变量计算

用SELECTEDVALUE替代MIN，精准获取当前筛选的单个campaign对应的上一年份：

testing = 
VAR extractCampaignYear = IF (ISFILTERED('Campaign Overview'[campaign]),SELECTEDVALUE('Campaign Overview'[previousExtractedYear]),0)

RETURN
SUMX(
    FILTER(
        SUMMARIZE (
            'Campaign Overview',
            'Campaign Overview'[campaign],
            "extractedCampaign",TRIM(MIN('Campaign Overview'[extractedCampaignName])),
            "campaignYear", MIN('Campaign Overview'[extractedYearFromCampaign]),
            "Sales", SUM ( 'Campaign Overview'[sales] )
        ),
        [campaignYear] = extractCampaignYear
    ), 
    [Sales]
)

方案2：支持多campaign筛选的动态匹配

把上一年度的计算放到SUMMARIZE的分组里，让每个campaign对应自己的上一年份，再动态求和：

testing = 
SUMX(
    SUMMARIZE (
        'Campaign Overview',
        'Campaign Overview'[campaign],
        "extractedCampaign",TRIM(MIN('Campaign Overview'[extractedCampaignName])),
        "campaignYear", MIN('Campaign Overview'[extractedYearFromCampaign]),
        "prevYear", MIN('Campaign Overview'[previousExtractedYear]),
        "Sales", SUM ( 'Campaign Overview'[sales] )
    ),
    IF(ISFILTERED('Campaign Overview'[campaign]),
        CALCULATE(SUM('Campaign Overview'[sales]),'Campaign Overview'[extractedYearFromCampaign] = [prevYear]),
        0
    )
)

方案3：统一数据类型

检查并确保previousExtractedYear和extractedYearFromCampaign数据类型一致，若存在文本转数值需求，用VALUE()转换：

VAR extractCampaignYear = IF (ISFILTERED('Campaign Overview'[campaign]),VALUE(MIN('Campaign Overview'[previousExtractedYear])),0)

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