Forum Discussion

Julius410's avatar
Julius410
Frequent Visitor
4 years ago

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

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

  • Julius410's avatar
    Julius410
    Frequent Visitor
    4 years ago

    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