DAX SUMMARIZE函数如何正确计算本年/上年度量值？

我参考YouTube视频使用DAX的SUMMARIZE()函数，该函数可正常汇总与事实表关联的维度表度量值。但添加“本年”“上年”过滤度量值列时，计算会忽略每个Disposal行的汇总范围，直接返回全年总计值。以下是我尝试的两个DAX查询、表关系及结果，请问如何编写查询才能让“本年”“上年”列尊重每个Disposal行的汇总值？

DAX查询(#1) #

Disposal Attributes (calc field) = 
VAR VolumeTotal = SUM('Piped Trucked Volume'[volume_bbls])
VAR vol_CurrentYear = CALCULATE(VolumeTotal, FILTER('Calendar', 'Calendar'[Year] = YEAR(TODAY())) )
VAR vol_PriorYear = CALCULATE(VolumeTotal, FILTER('Calendar', 'Calendar'[Year] = YEAR(TODAY())-1) )

var ListTotal = 
SUMMARIZE (
    'Disposal',
    [unified_disposalId],
    [Disposal (unified_name)],
    [Disposal (groups)],
    "Volume", FORMAT(VolumeTotal, "#,#"),
    "Vol_ThisYr", FORMAT(vol_CurrentYear, "#,#"),
    "Vol_PriorYr", FORMAT(vol_PriorYear, "#,#")
)

RETURN
FILTER( 
        ListTotal, VolumeTotal > 0
    )

DAX查询(#2) #

Disposal Attributes (calc field) = 
VAR VolumeTotal = SUM('Piped Trucked Volume'[volume_bbls])
VAR vol_CurrentYear = CALCULATE(VolumeTotal, FILTER('Calendar', 'Calendar'[Year] = YEAR(TODAY())) )
VAR vol_PriorYear = CALCULATE(VolumeTotal, FILTER('Calendar', 'Calendar'[Year] = YEAR(TODAY())-1) )

var ListTotal = 
SUMMARIZE (
    'Disposal',
    [unified_disposalId],
    [Disposal (unified_name)],
    [Disposal (groups)],
    "Volume", FORMAT(VolumeTotal, "#,#")
)

RETURN
ADDCOLUMNS(
    FILTER( 
        ListTotal, VolumeTotal > 0
    ),
    "Vol_ThisYr", FORMAT(vol_CurrentYear, "#,#"),
    "Vol_PriorYr", FORMAT(vol_PriorYear, "#,#")
)

表关系 #

查询结果 #

解决方案 #

问题根源在于你定义的VolumeTotal、vol_CurrentYear、vol_PriorYear变量是在整个模型的上下文中计算的，而非SUMMARIZE生成的每个Disposal行的上下文。要让年份过滤尊重每个维度行的范围，需要把计算逻辑放到行上下文内部，确保CALCULATE能继承当前Disposal的筛选上下文。

推荐写法 #

Disposal Attributes (calc field) = 
VAR CurrentYear = YEAR(TODAY())
VAR PriorYear = CurrentYear - 1

RETURN
ADDCOLUMNS(
    FILTER(
        SUMMARIZE (
            'Disposal',
            [unified_disposalId],
            [Disposal (unified_name)],
            [Disposal (groups)],
            "Volume", FORMAT(SUM('Piped Trucked Volume'[volume_bbls]), "#,#")
        ),
        SUM('Piped Trucked Volume'[volume_bbls]) > 0
    ),
    "Vol_ThisYr", 
    FORMAT(
        CALCULATE(
            SUM('Piped Trucked Volume'[volume_bbls]),
            'Calendar'[Year] = CurrentYear
        ),
        "#,#"
    ),
    "Vol_PriorYr", 
    FORMAT(
        CALCULATE(
            SUM('Piped Trucked Volume'[volume_bbls]),
            'Calendar'[Year] = PriorYear
        ),
        "#,#"
    )
)

写法说明 #

1. 提前定义年份变量，避免重复计算YEAR(TODAY())
2. 在SUMMARIZE内部直接计算总体积，确保基于当前Disposal行的上下文
3. 在ADDCOLUMNS中使用CALCULATE时，直接筛选Calendar[Year]，此时CALCULATE会自动继承当前Disposal行的筛选上下文，只计算该处置点对应年份的体积
4. 过滤逻辑放到SUMMARIZE之后的FILTER中，基于每个行的总体积判断，确保只保留有数据的行

替代写法（用SUMMARIZE直接生成所有列） #

Disposal Attributes (calc field) = 
VAR CurrentYear = YEAR(TODAY())
VAR PriorYear = CurrentYear - 1

RETURN
FILTER(
    SUMMARIZE (
        'Disposal',
        [unified_disposalId],
        [Disposal (unified_name)],
        [Disposal (groups)],
        "Volume", FORMAT(SUM('Piped Trucked Volume'[volume_bbls]), "#,#"),
        "Vol_ThisYr", FORMAT(CALCULATE(SUM('Piped Trucked Volume'[volume_bbls]), 'Calendar'[Year] = CurrentYear), "#,#"),
        "Vol_PriorYr", FORMAT(CALCULATE(SUM('Piped Trucked Volume'[volume_bbls]), 'Calendar'[Year] = PriorYear), "#,#")
    ),
    SUM('Piped Trucked Volume'[volume_bbls]) > 0
)

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