Power BI按类别、状态和日期取最大值及生成目标表求助

需求说明 #

现有一张按状态展示类别及开始、结束日期的表，需要生成每个类别仅一行的目标表，包含：

• 该类别最新Status（按Start日期最晚的行确定）
• 该类别最小StartDate
• 该类别最大EndDate

已尝试方法及问题 #

M语言条件列尝试 #

Logic= if [Status] = "InProgress" and [Start] = List.Max(#"Sorted Rows1"[Start]) and [End] = #datetime(2999, 12, 31, 0, 0, 0) then 2 else if [Status] = "Succeeded" then 1 else 0

• 问题：除CategoryG外其余正常，该类别应仅返回Logic值2，但当前同时返回Succeeded和InProgress的关联值，无法仅识别StartDate更晚的InProgress状态。

DAX标记列尝试 #

Flag DAX = SWITCH( TRUE (),
       'Table'[Start]= MAX ( 'Table'[Start] )
        && YEAR('Table'[End]) = 2999
        && 'Table'[Status] = "InProgress", 2,
        'Table'[Status] = "Failed", 1,
        'Table'[Start]= LASTNONBLANK(  'Table'[Start],1 )
        && 'Table'[Status] = "Succeeded" && YEAR('Table'[End]) <> 2999, 3,
        0
  )

• 问题：得到与M语言相同的错误结果，CategoryG的状态判断逻辑失效。

正确实现方法 #

方法1：Power Query（M语言）实现 #

核心是按类别分组后独立计算聚合值，步骤如下：

1. 进入Power Query编辑器，选中原始表
2. 点击「转换」→「分组依据」，配置规则：
   • 分组依据：选择你的类别列（如Category）
   • 新增列1：命名最小StartDate，操作选「最小值」，列选Start
   • 新增列2：命名最大EndDate，操作选「最大值」，列选End
   • 新增列3：命名最新Status，操作选「自定义」，输入以下代码：

     (rows as list) => 
     let
         // 组内行按Start日期降序排序
         sortedRows = List.Sort(rows, (a,b) => Value.Compare(b[Start], a[Start])),
         // 取排序后第一行的Status
         latestStatus = sortedRows{0}[Status]
     in
         latestStatus
     

3. 确定后即可得到每个类别一行的目标表。若需保留Logic标记，可新增条件列：

   Logic = 
   if [最新Status] = "InProgress" and [最大EndDate] = #datetime(2999, 12, 31, 0, 0, 0) then 2
   else if [最新Status] = "Failed" then 1
   else if [最新Status] = "Succeeded" then 3
   else 0
   

方法2：DAX计算表实现 #

使用SUMMARIZECOLUMNS按类别聚合，同时基于类别内最大Start日期获取最新状态：

目标表 = 
VAR 原始数据表 = 'Table'
RETURN
SUMMARIZECOLUMNS(
    原始数据表[Category],
    "最小StartDate", MIN(原始数据表[Start]),
    "最大EndDate", MAX(原始数据表[End]),
    "最新Status", 
    VAR 当前类别 = 原始数据表[Category]
    VAR 类别最大Start = CALCULATE(MAX(原始数据表[Start]), ALL(原始数据表), 原始数据表[Category] = 当前类别)
    RETURN CALCULATE(VALUES(原始数据表[Status]), 原始数据表[Start] = 类别最大Start)
)

若需添加Flag标记，在计算表中新增列：

Flag DAX = 
SWITCH(
    TRUE(),
    [最新Status] = "InProgress" && YEAR([最大EndDate]) = 2999, 2,
    [最新Status] = "Failed", 1,
    [最新Status] = "Succeeded" && YEAR([最大EndDate]) <> 2999, 3,
    0
)

关键修复点 #

之前的错误在于没有按类别单独计算Start日期最大值，而是取了整个表的全局最大值，导致CategoryG的状态判断逻辑失效。两种方法均确保每个类别内独立计算聚合值，解决了异常问题。

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