PowerBI-Grouping Events happening within 12 hours

[patienza]

Hello! I am fairly new working with PowerBI, I am struggling with creating the following dax.

I have a date column "Metric_DateTime" on my "All Loaded Incidents" table. I want to create a new column "Group ID" that groups all events happening within 12hours of each other under the same Group ID. Example below.  Thank you in advance for your help. 

Metric_DateTime	Group ID
2025-01-20 8:07	1
2025-01-20 15:02	1
2025-01-20 21:12	2
2025-01-21 18:01	3
2025-01-21 22:04	3
2025-01-22 11:00	4
2025-01-22 15:12	4
2025-01-24 15:00	4
2025-01-24 17:04	4
2025-01-25 8:00	5
2025-01-25 12:00	5
2025-01-25 13:24	5

[patienza · Accepted solution]

thank you very much for your solution and time. Again, Im new with BI so not exactly sure if I am looking at the right thing, but I tried to look at the PowerQuery but only see the data source error. Can you provide the solution in DAX form so I can add it as a new column?

[patienza]

thank you very much! this worked perfectly

[patienza · Accepted solution]

thank you very much for your solution and time. Again, Im new with BI so not exactly sure if I am looking at the right thing, but I tried to look at the PowerQuery but only see the data source error. Can you provide the solution in DAX form so I can add it as a new column?

[v-saisrao-msft · Accepted solution]

Hi @patienza,
To view the Power Query script in Power BI, open the Advanced Editor. Below is the DAX query, but ensure you make the necessary adjustments based on your column names and source.

Below is the Dax Query : 

let
    Source = Excel.Workbook(File.Contents("PATH"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Metric_DateTime", type datetime}}),

    // Sort data by Metric_DateTime
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Metric_DateTime", Order.Ascending}}),

    // Add an Index Column
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),

    // Add Previous Row's Metric_DateTime
    #"Added Prev Time" = Table.AddColumn(#"Added Index", "Prev Metric_DateTime", 
        each if [Index] = 1 then null 
             else #"Added Index"[Metric_DateTime]{[Index] - 2}, type nullable datetime),

    // Determine if a new group should start (if time difference > 12 hours)
    #"Added Group Flag" = Table.AddColumn(#"Added Prev Time", "New Group", 
        each if [Prev Metric_DateTime] = null 
             or Duration.TotalHours([Metric_DateTime] - [Prev Metric_DateTime]) > 12 then 1 else 0, 
        Int64.Type),

    // Generate Group ID using a running total of the "New Group" column
    #"Added Group ID" = Table.AddColumn(#"Added Group Flag", "Group ID", 
        each List.Sum(List.FirstN(#"Added Group Flag"[New Group], [Index])), 
        Int64.Type),
    // Remove unnecessary columns
    #"Removed Columns" = Table.RemoveColumns(#"Added Group ID",{"Index", "Prev Metric_DateTime", "New Group"})

in
    #"Removed Columns"

[patienza]

[v-saisrao-msft]

Hi @patienza,

Thank you for reaching out to Microsoft Fabric Community.

I have reproduced your scenario and attached the pbix file for your reference.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!