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
Anonymous
Not applicable

Equipment Runtime Proper

Capture.JPGHi 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 3
HotChilli
Super User
Super User

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 

TheoC
Super User
Super User

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

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Anonymous
Not applicable

@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 againCapture.JPG

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.