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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
frano72
Helper IV
Helper IV

Lower the grain of a fact table into 15 minute buckets

Hi, I have a fact table that records the states of trucks in a mining circuit.  Trucks change state from operating, waiting, lunch break etc.  The start and end time that the truck is in this state is one row in the table.

 

I need to reduce the grain of this table to a fixed 15 minutes for a report that represents the last 15 minute interval.  So if one row of the table represented the state of a truck as "operating" for 60 minutes then changing the grain to 15 minutes will see four rows.  Obviously gets a little more tricky when the state passes over the interval boundaries.

 

Any ideas/patterns on to reduce grain ?

 

(yeah i know...its not a finance example right !)

1 ACCEPTED SOLUTION

@frano72 - I would read that blog. Here is a solution just for you as promised. See attached PBIX file below sig. Created this table:

Table 2 = 
    VAR __Min = MIN('Table'[StartTimeUTC])
    VAR __Max = MAX('Table'[EndTimeUTC])
RETURN
    GENERATESERIES(__Min,__Max,1/24/60*15)

And then this column in that table:

Column = MAXX(FILTER('Table',[StartTimeUTC]>=[Value] && [EndTimeUTC]>=[Value]),[Status Name])

I love the operations stuff the best. OTIF, MTBF, etc. Wish I could have fit more in my book DAX Cookbook. You can see the ones I did include here: https://github.com/gdeckler/DAXCookbook Chapter 9.

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

@frano72 - You should be able to use a variation of Open Tickets to achieve this. Open Tickets is by hour or day I think but it can support any time granularity level. If you can post sample data I can whip up a version for you. Please @ me if you reply.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

 

For the record, the operations problems are way more interesting to me than the finance ones!!



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Thanks @Greg_Deckler - I'll have a look this morning at the tickets example.

 

[...maybe i should blog about operations examples to rebalance the weight of the worlds pbi finance examples !]

 

Here is a link to a subset of data in excel

https://www.dropbox.com/s/hczr2wxxh28pvpg/TruckStatus.xlsx?dl=0

 

Here's an even further subset.

StartTimeUTCEndTimeUTCShiftIndexDurationEquipment IDStatusIDStatus Name
31/07/2020 20:001/08/2020 0:532020080111761453418Ready
1/08/2020 0:531/08/2020 1:38202008011268053420Delay
1/08/2020 1:381/08/2020 6:382020080111803053419Standby
1/08/2020 6:381/08/2020 7:35202008011338553418Ready
1/08/2020 7:351/08/2020 8:00202008011149153420Delay

 

Basically there are two 12 hour shifts per day and this is represented with the ShiftIndex column.  The last digit represents night or day.  So the above table is for 1st Aug 2020, Day shift.   I have only shown one piece of equipment.

 

You'll notice that the sum of the Duration is 12 hrs (in seconds).

 

So the challenge is splitting these five rows which represent truck "statuses" over the 12 shift into 15 minute buckets and have the total for each status type.

 

So output would be something like this.  Each row is now 15 minutes (900 seconds).

 

There has to be some date logic somewhere when the status changes within the 15 minute bucket.

 

StartTimeUTCShiftIndexDurationEquipment IDStatus_ReadyStatus_DelayStatus_Standby
31/07/2020 20:002020080119005390000
31/07/2020 20:152020080119005390000
etc      

 

(the problem is a bit more than this, but have distilled it down to the simplest form, that once solved i can scale it to the more complex).

 

Thanks !

@frano72 - I would read that blog. Here is a solution just for you as promised. See attached PBIX file below sig. Created this table:

Table 2 = 
    VAR __Min = MIN('Table'[StartTimeUTC])
    VAR __Max = MAX('Table'[EndTimeUTC])
RETURN
    GENERATESERIES(__Min,__Max,1/24/60*15)

And then this column in that table:

Column = MAXX(FILTER('Table',[StartTimeUTC]>=[Value] && [EndTimeUTC]>=[Value]),[Status Name])

I love the operations stuff the best. OTIF, MTBF, etc. Wish I could have fit more in my book DAX Cookbook. You can see the ones I did include here: https://github.com/gdeckler/DAXCookbook Chapter 9.

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler - awesome work mate - thanks !!!!!!!

@frano72 - Let me know when you start your blog! 🙂



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Remember that a DateTime field is actually in decimal format with the integers as the days and the fractions as the hours and minutes etc.

 

Bucketing is very easy by applying some simple math

 

Half hour buckets:

 

ts_half = int([Timestamp]*48)/48
 
Quarter hour buckets:
 
ts_quart = int([Timestamp]*96)/96
 
etc.
amitchandak
Super User
Super User

@frano72 , Not very clear.

Create a time table and have a bucket of 15 Min in that and use that

Time Table - https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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