Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
uthall
Helper II
Helper II

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

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])))

View solution in original post

18 REPLIES 18
Mariusz
Community Champion
Community Champion

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.

@Mariusz 

 

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

Mariusz
Community Champion
Community Champion

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.

Thanks,

 

Did that and got

Capture.JPG

 

Mariusz
Community Champion
Community Champion

Hi @uthall 

 

isn't this what you wanted?

 

Many Thanks

Mariusz

No, i was trying to get the timer column populated.

Anyway, i got the code from your example, so all good.

Is there any way to group the counts per hour based on unique values.....like customer?

Hi,

Just to expand on this, i have the table as per below, which is good.

However:

1. Hours with no value are missing

2. Hour 17 for example has 21 entries, however, this is only for 3 customers. I want it to show only 3.

 

Possible?

 

Capture.JPG

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])))

Brilliant, works perfectly.

 

On last thing, the table now spreads across the 24 hr period, but is it possible to only show data to the current time?

 

eg. if its 1pm on the server, only show the data to 1pm?

Depends.  In your example are you saying that you want data from 2pm yesterday through 1pm today, or just data from midnight to 1pm?

 

The easiest way is to either put a date field on your records (so you have status at 10pm on Jan 1, 11pm Jan 1, 12am Jan 2, etc. Then throw a filter on the report or visual that only shows data from today - 24h or midnight to current time.  

 

This heavily depends on how you're getting/storing new data.  If there's no indicator in your data that shows what date it was collected, if you've got years of records, it will display all of that.

Thanks

Yep i have a date and time field.

I wasnt aware i could drop a filter that says today to now?

For sure you can create a filter like that. It depends on how you're storing current data. For example, if a process is going to run from 11a-2p, and it's 1p now, does that data have a Stop value? Do you display that in the visual at 1p? If Stop is blank until you populate the field when the process ends, then this part of the previous expression will evaluate false, and you won't get that entry until it finishes.

[Stop]>=SELECTEDVALUE(Table2[Time])

What if a process runs overnight or over 3+ days?  If it starts at 11p on Jan 1 and ends at 2a on Jan 2, do you display that on Jan 2?  This can cause issues because if you only have one date on the record, and we filter on that date, the overnight process will appear in either the previous day's data or the current day's data, but not both unless you set up exceptions in your DAX.  Just things to think about/plan for when setting up your data.

 

Setting up a report or visual level filter based on time is exceedingly easy.  On the right hand side, drag your Date value into the filter section, pick Relative date filtering from the dropdown, and set it so that it only shows items in the current day (or whatever date range you want).

snipa.PNG

Thanks,

Used the "In this day" filter, but funnily enough, when i hit midnight, the "in this day" filter continues to apply yersterdays date range, untill like 6 hours later.

How does the report apply this filter?

 

 

Cmcmahan
Resident Rockstar
Resident Rockstar

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])

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?

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.

Thanks Lin and Cmcmahan 

 

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.