DAX总结这一年最后一年的措施

Question

我使用的是DAX SUMMARIZE()，如这个YT视频所示。它可以很好地总结维度表与事实表之间的关系。但是，当我尝试为“今年”和“去年”列引入过滤措施时，计算忽略了汇总的列，并计算了全年。

如何编写查询以添加尊重汇总值的“今年”和“去年”列。

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, "#,#")
)

关系>

​

​

结果>

​

​

Answer 1

使用"VAR vol_CurrentYear“，您总是得到相同的值。要将计算放入行上下文，请使用ADDCOLUMNS()中的context ()。您可以创建度量而不是变量，然后在ADDCOLUMNS中使用它们，它将具有相同的效果。

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(CALCULATE(VolumeTotal, FILTER('Calendar', 'Calendar'[Year] = YEAR(TODAY())) ), "#,#"),
    "Vol_PriorYr", FORMAT(CALCULATE(VolumeTotal, FILTER('Calendar', 'Calendar'[Year] = YEAR(TODAY())-1) ), "#,#")
)