cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Grouping records per hour

Hello,

I have a table with records with a start time and stop time

I want to sum the total records per hour.

For example, record 1 starts at 1300 and stops as 1500, and record 2 starts at 1300 and finshes at 1700, then i want to see hour 13 with 2 records, hour 14 with 2 records, hour 15 with 2 records, hour 16 with 1 record, and hour 17 with 1 record.

Is this possible?

1 ACCEPTED SOLUTION

Accepted Solutions
New Contributor

## Re: Grouping records per hour

If you want to show blanks, then on the table options, click the dropdown on the Hour field, and check the option to "Show items with no data"

If you want to show a 0 instead of a blank, you can change the previous measure to:

`Measure 2 = COUNTAX(FILTER(Table1,[Start]<=SELECTEDVALUE(Table2[Time])&&[Stop]>=SELECTEDVALUE(Table2[Time])),[RecordID])+0`

As far as splitting by RecordID vs Customer, you never mentioned that the data was also split into customers.  How are RecordID and customer related?  If you want it to only count Customers, you can replace [RecordID] in the above query with DISTINCT([CustomerID]) instead.

Here's how you would get a count of distinct Customers instead of a count of records:

`Measure 3 = CALCULATE(DISTINCTCOUNT(Table1[CustomerID])+0,FILTER(Table1,[Start]<=SELECTEDVALUE(Table2[Time])&&[Stop]>=SELECTEDVALUE(Table2[Time])))`
18 REPLIES 18
New Contributor

## Re: Grouping records per hour

I'm assuming you have a table that lists each of the hours already that you can create this measure on.  This formula assumes Table1 has [RecordID], [Start], and [Stop] entries, while Table2 is a list of hours (1300, 1400, 1500, etc) in a DATETIME format.

`Measure2 = COUNTAX(FILTER(Table,[Start]<=SELECTEDVALUE(Table2[Time])&&[Stop]>=SELECTEDVALUE(Table2[Time])),[RecordID])`
Regular Visitor

## Re: Grouping records per hour

Thanks,

No, i dont have a table of hours over a day (24hrs).

Is it possible without this, or do i need to create a specific table just for this?

Community Support Team

## Re: Grouping records per hour

hi, @uthall

You need to define a dim table of hours over a day (24hrs) In advance as below:

Then use the measure as above

`Measure = COUNTAX(FILTER(Table1,[Start]<=SELECTEDVALUE('Dim time'[Time])&&[Stop]>=SELECTEDVALUE('Dim time'[Time])),[RecordID])`

Now drag Time field from dim table and the meausre into visual

Result:

and here is pbix file, please try it.

Also here is another similar post for you refer to:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
New Contributor

## Re: Grouping records per hour

You need a specific table just for this. PowerBI doesn't have a way to iterate over data that isn't in a table.  There's no while or for loops.

Creating the dimension table mentioned above is the only way to  easily accomplish this.   If it helps, you can hide the table from report views once you've created it.

Regular Visitor

## Re: Grouping records per hour

Thanks Lin and Cmcmahan

Is there any way to create the dimension table other than manually?

Senior Member

## Re: Grouping records per hour

Hi @uthall

You can use Query editor for that, please see the M code below.

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0tjIwACIQ0xTKjNWJVjJCkzOHycUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Start = _t, End = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Start", type time}, {"End", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "TimeRecords", each List.Times( [Start], Duration.Hours( Duration.From( [End] - [Start] ) ) + 1, #duration(0, 1, 0, 0))),
#"Expanded TimeRecords" = Table.ExpandListColumn(#"Added Custom", "TimeRecords"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded TimeRecords",{{"TimeRecords", type time}})
in
#"Changed Type2"```

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regular Visitor

## Re: Grouping records per hour

Sorry to be a dodo........can you briefly explain how i add the query to a new table in the query editor...

Senior Member

## Re: Grouping records per hour

Hi @uthall

In query editor, go to
Home > New Source > Blank Query

as Below

Next, go to Home > Advanced Editor / and paste the code provided there.

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regular Visitor

Thanks,

Did that and got