Forum Discussion
Equipment Runtime Proper
Hi there, so I receive data in date column {datetime} in the format –
28-October-2021 8:15 PM.
I get the data in 15 minute intervals 15,30,45,00 min. When the equipment is fully up and running, I receive around 60 – 100 events in the {description} column according to throughput, rejects and stoppages, all with the same time stamp as the above 28-October-2021 8:15 PM. This will happen for the next time stamp - 28-October-2021 8:30… etc etc
To rule out any maintenance testing as runtime, I only want to count each time stamp [datetime], if there is greater than 10 events within that timestamp. My current measure is
Hour Runtime = ([15m count]/4)+ 0
Hope this isn’t too confusing, and thanks in advance
3 Replies
- TheoC
Community Champion
Hi Anonymous
A few ways of doing this, one of which is as follows:
Occurence = COUNTX ( FILTER ( Table, EARLIER ( Table[Column] ) = Table[Column] ), Table[Column] )
Hope this helps 🙂
Theo
TheoC Thank you Theo, I think I made it too confusing in my description. Basically I want to do a DISTINCTCOUNT in the datetime column to add my 15min increments (x4 for the hour) for equipment runtime, BUT exclude any with < 5 because i dont want to include in the runtime, as per the example. Can you think of a way i can do this? Thankyou again
- HotChilli
If I understand you correctly, in Power Query:
Do a 'Group By' on the datetime column.
Use 2 aggregations: 1 for Count rows, 1 for All Rows.
Filter (from the column header) the Count column to be greater than 5.
Expand the 'all' column header to bring your data back
