cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
uthall Regular Visitor
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
Cmcmahan New Contributor
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
Cmcmahan New Contributor
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])
uthall Regular Visitor
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
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:

3.JPG

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:

11.JPG

 

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.
Cmcmahan New Contributor
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.

uthall Regular Visitor
Regular Visitor

Re: Grouping records per hour

Thanks Lin and Cmcmahan 

 

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

Mariusz Senior Member
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.

uthall Regular Visitor
Regular Visitor

Re: Grouping records per hour

@Mariusz 

 

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

Mariusz Senior Member
Senior Member

Re: Grouping records per hour

Hi @uthall 

 

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

as Below

image.png

 

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.

uthall Regular Visitor
Regular Visitor

Re: Grouping records per hour

Thanks,

 

Did that and got

Capture.JPG