Power BI中基于DAX/Power Query实现连续合同分组标记需求

针对你50万行数据的合同分组需求，以下提供两种可行方案，优先推荐Power Query预处理（性能更适配大数据量），同时补充DAX计算列方案作为备选。

一、Power Query预处理方案（推荐） #

Power Query的批处理逻辑更适合大规模数据，步骤如下：

1. 进入Power Query编辑器：在Power BI中导入数据集后，点击「转换数据」进入编辑器。
2. 聚合同一Contract ID的时间范围：先合并同一Contract ID下的所有时间区间，避免重复处理：

   ContractAggregated = Table.Group(
       源,
       {"Contract ID"},
       {
           {"Min Start Date", each List.Min([Start Date]), type date},
           {"Max End Date", each List.Max([End Date]), type date}
       }
   )
   

3. 排序并标记连续合同组：按起始日期排序后，通过循环找到所有时间连续的合同链，标记每个Contract ID所属的根Group ID（组内最早的Contract ID）：

   SortedContracts = Table.Sort(ContractAggregated,{{"Min Start Date", Order.Ascending}}),
   Indexed = Table.AddIndexColumn(SortedContracts, "Index", 0, 1, Int64.Type),
   WithRootID = Table.AddColumn(Indexed, "Root Contract ID", each [Contract ID]),
   FinalAggregated = List.Accumulate(
       List.Skip(Indexed[Index]),
       WithRootID,
       (state, currentIndex) =>
           let
               CurrentRow = Table.SelectRows(state, each [Index] = currentIndex){0},
               PreviousRows = Table.SelectRows(state, each [Index] < currentIndex),
               MatchingPrevious = Table.SelectRows(PreviousRows, each [Max End Date] = CurrentRow[Min Start Date]),
               NewRootID = if Table.RowCount(MatchingPrevious) > 0 then MatchingPrevious[Root Contract ID]{0} else CurrentRow[Root Contract ID],
               UpdatedState = Table.ReplaceValue(
                   state,
                   CurrentRow,
                   Record.ReplaceFields(CurrentRow, {"Root Contract ID", NewRootID}),
                   Replacer.ReplaceValue,
                   {"Index"}
               )
           in
               UpdatedState
   )
   

4. 合并回原数据集：将聚合得到的Root ID关联到原始数据，生成Group ID列：

   FinalTable = Table.NestedJoin(
       源,
       {"Contract ID"},
       FinalAggregated,
       {"Contract ID"},
       "GroupData",
       JoinKind.LeftOuter
   ),
   AddedGroupID = Table.AddColumn(FinalTable, "Group ID", each [GroupData][Root Contract ID]{0}),
   CleanedTable = Table.RemoveColumns(AddedGroupID, {"GroupData"})
   

5. 加载数据：关闭编辑器，将处理后的数据加载回Power BI，即可得到带Group ID的完整数据集。

二、DAX计算列方案（小数据量适用） #

若无需修改数据源，可通过DAX计算列实现，但50万行数据可能存在性能瓶颈，需谨慎使用：

创建计算列Group ID：

Group ID = 
VAR CurrentStart = 'Table'[Start Date]
VAR CurrentContract = 'Table'[Contract ID]
// 递归追溯连续合同链的最早Contract ID
VAR RootContract = 
    CALCULATE(
        MIN('Table'[Contract ID]),
        FILTER(
            ALL('Table'),
            'Table'[End Date] <= CurrentStart && 
            EXISTS(
                FILTER(
                    ALL('Table'),
                    'Table'[Start Date] = EARLIER('Table'[End Date]) && 
                    'Table'[Contract ID] <> EARLIER('Table'[Contract ID])
                )
            ) || 
            'Table'[Contract ID] = CurrentContract
        )
    )
RETURN
    IF(ISBLANK(RootContract), CurrentContract, RootContract)

验证说明 #

用你提供的示例数据测试两种方案，均可得到期望的Group ID结果：

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