Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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 !)
Solved! Go to 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.
@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!!
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.
StartTimeUTC | EndTimeUTC | ShiftIndex | Duration | Equipment ID | StatusID | Status Name |
31/07/2020 20:00 | 1/08/2020 0:53 | 202008011 | 17614 | 53 | 418 | Ready |
1/08/2020 0:53 | 1/08/2020 1:38 | 202008011 | 2680 | 53 | 420 | Delay |
1/08/2020 1:38 | 1/08/2020 6:38 | 202008011 | 18030 | 53 | 419 | Standby |
1/08/2020 6:38 | 1/08/2020 7:35 | 202008011 | 3385 | 53 | 418 | Ready |
1/08/2020 7:35 | 1/08/2020 8:00 | 202008011 | 1491 | 53 | 420 | Delay |
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.
StartTimeUTC | ShiftIndex | Duration | Equipment ID | Status_Ready | Status_Delay | Status_Standby |
31/07/2020 20:00 | 202008011 | 900 | 53 | 900 | 0 | 0 |
31/07/2020 20:15 | 202008011 | 900 | 53 | 900 | 0 | 0 |
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.
@frano72 - Let me know when you start your blog! 🙂
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:
@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/
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
80 | |
64 | |
52 | |
48 |
User | Count |
---|---|
213 | |
89 | |
77 | |
66 | |
60 |