Power BI中如何按月份添加小时级聚合最大值标记？

问题背景 #

我有一个Power BI报表，展示每小时时间周期的数量总和，现有时间序列可视化图表显示各时段数量，且带有全选时段Top N值标记。现在需要添加每月最大值标记，但遇到困难，终端用户需能通过DateTimeColumn、ID和Product进行筛选。尝试相关DAX代码时报错提示不是有效表，用SUMMARIZECOLUMNS也出现同样问题，困惑于如何在切片器过滤下实现聚合嵌套操作。另外，除了按月份聚合的解决方案，还想了解更通用的实现方法（比如按Product维度），获取相关示例或讲解，理解此类问题的解决思路。

附现有代码参考 #

示例表DAX代码 #

SampleTable = 
DATATABLE (
    "DateTimeColumn", DATETIME,
    "ID", INTEGER,
    "Product", STRING,
    "Quantity", INTEGER,
    {
        { DATETIME(2024,1,1,8,0,0), 1, "A", 10 },
        { DATETIME(2024,1,1,9,0,0), 1, "A", 15 },
        { DATETIME(2024,1,31,10,0,0), 1, "A", 20 },
        { DATETIME(2024,2,1,11,0,0), 1, "A", 18 },
        { DATETIME(2024,2,28,12,0,0), 1, "A", 22 },
        { DATETIME(2024,1,1,8,0,0), 2, "B", 8 },
        { DATETIME(2024,1,1,9,0,0), 2, "B", 12 },
        { DATETIME(2024,1,31,10,0,0), 2, "B", 16 },
        { DATETIME(2024,2,1,11,0,0), 2, "B", 14 },
        { DATETIME(2024,2,28,12,0,0), 2, "B", 19 }
    }
)

现有全时段Top N峰值标记DAX代码（改编自Guy In A Cube视频，NbPeaks为参数） #

TopN Peaks = 
VAR TotalQuantity = SUM(SampleTable[Quantity])
VAR RankedValues = 
    RANKX(
        ALLSELECTED(SampleTable[DateTimeColumn]),
        CALCULATE(SUM(SampleTable[Quantity])),
        , DESC, Dense
    )
RETURN
    IF(RankedValues <= [NbPeaks], TotalQuantity, BLANK())

T-SQL实现示例 #

WITH HourlyData AS (
    SELECT 
        DATEFROMPARTS(YEAR(DateTimeColumn), MONTH(DateTimeColumn), DAY(DateTimeColumn)) + 
        CAST(DATEPART(HOUR, DateTimeColumn) AS VARCHAR) + ':00:00' AS HourPeriod,
        MONTH(DateTimeColumn) AS MonthNum,
        Product,
        ID,
        SUM(Quantity) AS HourlyTotal
    FROM SampleTable
    GROUP BY 
        DATEFROMPARTS(YEAR(DateTimeColumn), MONTH(DateTimeColumn), DAY(DateTimeColumn)) + 
        CAST(DATEPART(HOUR, DateTimeColumn) AS VARCHAR) + ':00:00',
        MONTH(DateTimeColumn),
        Product,
        ID
),
MonthlyMax AS (
    SELECT 
        MonthNum,
        Product,
        ID,
        MAX(HourlyTotal) AS MaxHourlyInMonth
    FROM HourlyData
    GROUP BY MonthNum, Product, ID
)
SELECT 
    h.HourPeriod,
    h.MonthNum,
    h.Product,
    h.ID,
    h.HourlyTotal,
    CASE WHEN h.HourlyTotal = m.MaxHourlyInMonth THEN h.HourlyTotal ELSE NULL END AS MonthlyPeak
FROM HourlyData h
JOIN MonthlyMax m 
    ON h.MonthNum = m.MonthNum 
    AND h.Product = m.Product 
    AND h.ID = m.ID

解决方案：按月份标记最大值 #

DAX度量值实现 #

Monthly Peak = 
VAR CurrentHourTotal = SUM(SampleTable[Quantity])
VAR CurrentContext = 
    SELECTCOLUMNS(
        CURRENTGROUP(),
        "@Month", EOMONTH(SampleTable[DateTimeColumn], 0),
        "@Product", SampleTable[Product],
        "@ID", SampleTable[ID]
    )
VAR MonthlyMaxTotal = 
    CALCULATE(
        MAXX(
            SUMMARIZE(
                ALLSELECTED(SampleTable),
                SampleTable[DateTimeColumn],
                SampleTable[Product],
                SampleTable[ID],
                "@HourTotal", SUM(SampleTable[Quantity])
            ),
            [@HourTotal]
        ),
        TREATAS(CurrentContext, SampleTable[DateTimeColumn], SampleTable[Product], SampleTable[ID])
    )
RETURN
    IF(CurrentHourTotal = MonthlyMaxTotal, CurrentHourTotal, BLANK())

思路说明 #

1. CurrentHourTotal：计算当前小时时段的数量总和
2. CurrentContext：捕获当前筛选上下文里的月份（用EOMONTH统一当月最后一天作为月份标识）、Product和ID
3. MonthlyMaxTotal：在所有选中的数据范围内，按月份、Product、ID分组计算每小时总和，再取每组的最大值
4. 最后判断当前小时总和是否等于该组最大值，是则返回数值，否则返回空值

通用维度峰值标记方法（以Product为例） #

DAX度量值实现 #

Product Peak = 
VAR CurrentHourTotal = SUM(SampleTable[Quantity])
VAR CurrentContext = 
    SELECTCOLUMNS(
        CURRENTGROUP(),
        "@Product", SampleTable[Product],
        "@ID", SampleTable[ID]
    )
VAR ProductMaxTotal = 
    CALCULATE(
        MAXX(
            SUMMARIZE(
                ALLSELECTED(SampleTable),
                SampleTable[DateTimeColumn],
                SampleTable[Product],
                SampleTable[ID],
                "@HourTotal", SUM(SampleTable[Quantity])
            ),
            [@HourTotal]
        ),
        TREATAS(CurrentContext, SampleTable[Product], SampleTable[ID])
    )
RETURN
    IF(CurrentHourTotal = ProductMaxTotal, CurrentHourTotal, BLANK())

通用思路总结 #

1. 捕获上下文：用CURRENTGROUP()或SELECTEDVALUE获取当前需要分组的维度（如月份、Product、ID等）
2. 聚合计算：在ALLSELECTED()范围内，先按小时+分组维度聚合每小时总和，再用MAXX/MINX等迭代函数计算每组的最大值
3. 匹配判断：将当前小时总和与分组最大值对比，标记峰值
4. 适配筛选器：ALLSELECTED()保证切片器筛选生效，TREATAS()将当前上下文传递到聚合计算中，确保分组正确

常见错误解决 #

• 报错“不是有效表”：通常是因为直接在CALCULATE中使用了非表表达式，需确保聚合操作（如SUMMARIZE、GROUPBY）返回有效表后再用MAXX/MINX等迭代函数计算
• SUMMARIZECOLUMNS问题：该函数不支持在度量值中直接配合CURRENTGROUP()使用，改用SUMMARIZE+ALLSELECTED更适配可视化上下文

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