Time Specific Events

[Julius410]

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/2021	12:02:30 AM	Machine 2	STOP
17/03/2021	12:14:25 AM	Machine 1	Arms not closed
17/03/2021	12:14:26 AM	Machine 1	Arms not closed
17/03/2021	12:14:28 AM	Machine 1	Arms not closed
17/03/2021	12:14:57 AM	Machine 1	Arms not closed
17/03/2021	12:38:05 AM	Machine 1	Arms not closed
17/03/2021	12:42:06 AM	Machine 1	Arms not closed
17/03/2021	12:43:06 AM	Machine 1	Arms not closed
17/03/2021	12:43:24 AM	Machine 1	Arms not closed
17/03/2021	1:21:57 AM	Machine 2	STOP
17/03/2021	1:56:50 AM	Machine 2	STOP
17/03/2021	2:38:14 AM	Machine 2	STOP
17/03/2021	2:52:39 AM	Machine 1	Arm fault
17/03/2021	2:54:30 AM	Machine 2	STOP
17/03/2021	3:00:14 AM	Machine 1	Arm fault
17/03/2021	3:17:03 AM	Machine 2	STOP
17/03/2021	3:24:29 AM	Machine 1	Arm fault
17/03/2021	3:28:22 AM	Machine 2	STOP
17/03/2021	3:38:20 AM	Machine 2	STOP
17/03/2021	4:18:54 AM	Machine 2	STOP
17/03/2021	4:39:01 AM	Machine 1	Arm fault
17/03/2021	4:40:45 AM	Machine 2	STOP
17/03/2021	5:05:20 AM	Machine 1	Motor fault
17/03/2021	9:56:11 PM	Machine 1	Top Limit reached
17/03/2021	00:03:44	Machine 3	Error Code C19
17/03/2021	00:03:46	Machine 3	Error Code C2
17/03/2021	00:03:52	Machine 3	Error Code C3
17/03/2021	00:05:25	Machine 3	Error Code C19
17/03/2021	00:05:27	Machine 3	Error Code C2
17/03/2021	00:05:33	Machine 3	Error Code C3
17/03/2021	00:07:53	Machine 3	Error Code C19
17/03/2021	00:07:55	Machine 3	Error Code C2
17/03/2021	00:08:01	Machine 3	Error Code C3
17/03/2021	00:08:58	Machine 3	Error Code C19
17/03/2021	00:09:00	Machine 3	Error Code C2
17/03/2021	00:09:06	Machine 3	Error Code C3
17/03/2021	00:10:22	Machine 3	Error Code C19
17/03/2021	00:10:24	Machine 3	Error Code C2
17/03/2021	00:10:30	Machine 3	Error Code C3
17/03/2021	00:11:39	Machine 3	Pick-up error
17/03/2021	00:12:22	Machine 3	Clear carton
17/03/2021	01:04:21	Machine 3	Backup chain not clear
17/03/2021	01:53:10	Machine 3	Belt good not clear
17/03/2021	01:54:36	Machine 3	Error Code C19
17/03/2021	01:54:38	Machine 2	STOP
17/03/2021	01:54:43	Machine 3	Error Code C3
17/03/2021	02:00:47	Machine 3	Backup chain not clear
17/03/2021	02:03:20	Machine 3	Error Code C19
17/03/2021	02:03:22	Machine 3	Error Code C2
17/03/2021	02:03:25	Machine 3	Error Code C3
17/03/2021	02:05:33	Machine 2	STOP

[Julius410]

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:

https://www.dropbox.com/scl/fi/xeghphf7de0ikn39gdgk9/Mock-Data.xlsx?dl=0&rlkey=y3wp99mwb1yddube2i9b4...

Please see below a link to my current Power BI working file:

https://www.dropbox.com/s/cklczwscixo63la/Test_28022022.pbix?dl=0 

Again, thank you for your help.

Julius

[amitchandak]

@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]))