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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Julius410
Frequent Visitor

Time Specific Events

Hi everyone,

 

I am having trouble to create a solution that allows me to identify the top three contributing factors to a machine stop. The only events I would like to consider are events that happened 6 minutes before the machine stopped. This is my scenario:

 

  • I would like to understand the top three events that cause machine 2 to stop working
  • On a daily basis, I receive three reports from three different machines that are combined in one table (see below)
  • I know that the potential events, which caused the stop in machine 2, can appear in machine 1 and 3 within a timeframe of 6 minutes before the stop event

 

How can I identify the top events that caused machine 2 to stop (only considering events that happened 6 minutes before the stop in machine 2 and 3)?

 

Please see the table below for an example with mock data.

 

Thank you.

 

Date              Time               Machine     Event

17/03/202112:02:30 AMMachine 2STOP
17/03/202112:14:25 AMMachine 1Arms not closed
17/03/202112:14:26 AMMachine 1Arms not closed
17/03/202112:14:28 AMMachine 1Arms not closed
17/03/202112:14:57 AMMachine 1Arms not closed
17/03/202112:38:05 AMMachine 1Arms not closed
17/03/202112:42:06 AMMachine 1Arms not closed
17/03/202112:43:06 AMMachine 1Arms not closed
17/03/202112:43:24 AMMachine 1Arms not closed
17/03/20211:21:57 AMMachine 2STOP
17/03/20211:56:50 AMMachine 2STOP
17/03/20212:38:14 AMMachine 2STOP
17/03/20212:52:39 AMMachine 1Arm fault
17/03/20212:54:30 AMMachine 2STOP
17/03/20213:00:14 AMMachine 1Arm fault
17/03/20213:17:03 AMMachine 2STOP
17/03/20213:24:29 AMMachine 1Arm fault
17/03/20213:28:22 AMMachine 2STOP
17/03/20213:38:20 AMMachine 2STOP
17/03/20214:18:54 AMMachine 2STOP
17/03/20214:39:01 AMMachine 1Arm fault
17/03/20214:40:45 AMMachine 2STOP
17/03/20215:05:20 AMMachine 1Motor fault
17/03/20219:56:11 PMMachine 1Top Limit reached
17/03/2021 00:03:44 Machine 3Error Code C19
17/03/2021 00:03:46 Machine 3Error Code C2
17/03/2021 00:03:52 Machine 3Error Code C3
17/03/2021 00:05:25 Machine 3Error Code C19
17/03/2021 00:05:27 Machine 3Error Code C2
17/03/2021 00:05:33 Machine 3Error Code C3
17/03/2021 00:07:53 Machine 3Error Code C19
17/03/2021 00:07:55 Machine 3Error Code C2
17/03/2021 00:08:01 Machine 3Error Code C3
17/03/2021 00:08:58 Machine 3Error Code C19
17/03/2021 00:09:00 Machine 3Error Code C2
17/03/2021 00:09:06 Machine 3Error Code C3
17/03/2021 00:10:22 Machine 3Error Code C19
17/03/2021 00:10:24 Machine 3Error Code C2
17/03/2021 00:10:30 Machine 3Error Code C3
17/03/2021 00:11:39 Machine 3Pick-up error
17/03/2021 00:12:22 Machine 3Clear carton
17/03/2021 01:04:21 Machine 3Backup chain not clear
17/03/2021 01:53:10 Machine 3Belt good not clear
17/03/2021 01:54:36 Machine 3Error Code C19
17/03/2021 01:54:38 Machine 2STOP
17/03/2021 01:54:43 Machine 3Error Code C3
17/03/2021 02:00:47 Machine 3Backup chain not clear
17/03/2021 02:03:20 Machine 3Error Code C19
17/03/2021 02:03:22 Machine 3Error Code C2
17/03/2021 02:03:25 Machine 3Error Code C3
17/03/2021 02:05:33 Machine 2STOP
2 REPLIES 2
Julius410
Frequent Visitor

Hi @amitchandak,

 

Thanks so much for your quick response.

 

Unfortunately, your code does not present the desired results. Here is what I entered:

 

Measure 1 = CALCULATE(COUNTROWS(Table1),FILTER(Table1, Table1[Time] >= NOW() - TIME(6,0,0) && Table1[Machine] = "Machine 2"))
 
Top Events = CALCULATE([Measure 1], TOPN(2, ALLSELECTED(Table1[Event]), [Measure 1], DESC), VALUES(Table1[Event]))
 
When I put the the "Top Events" measure in a table together with the column "Event", I get the error code: "Can't display the visual."
 
I tried to understand your code bit by bit and I am not sure if it would display what I am trying to figure out. Just to clarify, I am trying to find the most popular events in Machine 1 & 3 that appeared 6 minutes before the stop event in Machine 2.
 
Please see below a link to the mock dataset:
 
 
Please see below a link to my current Power BI working file:
 
 
Again, thank you for your help.
 
Julius
amitchandak
Super User
Super User

@Julius410 , Use the second measure with event in the table visual

 

M1= calculate(countrows(table), filter(Table, Table[Date Time] >= now() -time(6,0,0) && Table[Machine] = "Machine2"))

Top 2 =calculate([M1], TOPN(2,allselected(Table[Event]),[M1],DESC), values(Table[Event]))

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors