topic Re: PowerBI-Grouping Events happening within 12 hours in DAX Commands and Tips

PowerBI-Grouping Events happening within 12 hours

Anonymous — Thu, 03 Apr 2025 02:43:35 GMT

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

Re: PowerBI-Grouping Events happening within 12 hours

Anonymous — Thu, 03 Apr 2025 02:51:01 GMT

Re: PowerBI-Grouping Events happening within 12 hours

v-saisrao-msft — Thu, 03 Apr 2025 07:32:26 GMT

Hi Anonymous,

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!

Re: PowerBI-Grouping Events happening within 12 hours

Anonymous — Thu, 03 Apr 2025 07:58:17 GMT

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?

Re: PowerBI-Grouping Events happening within 12 hours

v-saisrao-msft — Thu, 03 Apr 2025 08:39:53 GMT

Hi Anonymous,
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"

Re: PowerBI-Grouping Events happening within 12 hours

Anonymous — Fri, 04 Apr 2025 03:00:03 GMT

thank you very much! this worked perfectly