Forum Discussion

Smallegrue's avatar
Smallegrue
Frequent Visitor
6 years ago
Solved

Referencing rows within same table based on events

Hello all,

 

I've been having trouble with this and I can't seem to figure it out.

 

I have an equipement status table that records a row everytime an equipement status changes or at the end of a shift. It currently looks like this:

 

Row IDDateEquipmentShiftTimeDurationStatus
12020-02-17T101Night0:0043200Up
22020-02-18T101Day0:0021600Up
32020-02-18T101Day6:0010800Down
42020-02-18T101Day9:0010800Up
52020-02-18T101Night0:0043200Down
62020-02-19T101Day0:0043200Down
72020-02-19T101Night0:0043200Down
82020-02-20T101Day0:0043200Down
92020-02-20T101Night0:0043200Down

 

The problem is we are trying to calculate the mean time to repair. If you notice the last 5 rows of the table are all associated to the same failure. Ideally, I would like to add a column that would associate the root failure based on the previous status change/not change. Something like this:

 

Row IDDateEquipmentShiftTimeDurationStatusOriginal Failure line ID
12020-02-17T101Night0:0043200Up 
22020-02-18T101Day0:0021600Up 
32020-02-18T101Day6:0010800Down3
42020-02-18T101Day9:0010800Up 
52020-02-18T101Night0:0043200Down5
62020-02-19T101Day0:0043200Down5
72020-02-19T101Night0:0043200Down5
82020-02-20T101Day0:0043200Down5
92020-02-20T101Night0:0043200Down5

 

Can someone please help me out with this I can't figure it out.

 

Thank you,

 

Steph

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Smallegrue ,

     

    We can create a calculated column to meet your requirement:

     

    Original Failure line ID =
IF (
    [Status] = "Down",
    CALCULATE (
        MIN ( 'Table'[Row ID] ),
        FILTER (
            'Table',
            'Table'[Row ID]
                > CALCULATE (
                    MAX ( 'Table'[Row ID] ),
                    FILTER (
                        'Table',
                        'Table'[Row ID] <= EARLIER ( 'Table'[Row ID], 2 )
                            && 'Table'[Status] = "Up"
                    )
                )
                && 'Table'[Status] = "Down"
        )
    )
)

     

     


    Best regards,

     

4 Replies

    • Smallegrue's avatar
      Smallegrue
      Frequent Visitor
      6 years ago

      Thanks Greg,

       

      I'll take a look at it! I'll let you know.

    • Smallegrue's avatar
      Smallegrue
      Frequent Visitor
      6 years ago

      Hello,

       

      So I looked at Greg_Deckler post and although it is a start, I feel it's not exactly the same thing. I maybe mistaken, but I feel Greg's post deals with rows that are independant of each other vs my rows are dependant on each other...the lenght of a failure could be the sum of multiple durations of subsequent lines. In greg's case each line represents a distinct failure.

       

      Using Greg's post I was able to "tag" failing lines, but I'm not able to distinct each failure.

       

      Here is the closest result I was able to obtain...

       

      Original Failure Line ID =
      minx(
      filter(
      'Status Equipements',
      'Status Equipements'[Equipment] = EARLIER('Status Equipements'[Equipment])
      && 'Status Equipements'[Status] = EARLIER('Status Equipements'[status])
      && 'Status Equipements'[Status] = "Down"
      && 'Status Equipements'[Row ID] <= earlier('Status Equipements'[Row ID])
       
      ),
      'Status Equipements'[Row ID]
      )

       

       

      Row IDDateEquipmentShiftTimeDurationStatusOriginal Failure line ID
      12020-02-17T101Night0:0043200Up 
      22020-02-18T101Day0:0021600Up 
      32020-02-18T101Day6:0010800Down3
      42020-02-18T101Day9:0010800Up 
      52020-02-18T101Night0:0043200Down3
      62020-02-19T101Day0:0043200Down3
      72020-02-19T101Night0:0043200Down3
      82020-02-20T101Day0:0043200Down3
      92020-02-20T101Night0:0043200Down3

       

       

      Any help is appreciated

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi Smallegrue ,

         

        We can create a calculated column to meet your requirement:

         

        Original Failure line ID =
IF (
    [Status] = "Down",
    CALCULATE (
        MIN ( 'Table'[Row ID] ),
        FILTER (
            'Table',
            'Table'[Row ID]
                > CALCULATE (
                    MAX ( 'Table'[Row ID] ),
                    FILTER (
                        'Table',
                        'Table'[Row ID] <= EARLIER ( 'Table'[Row ID], 2 )
                            && 'Table'[Status] = "Up"
                    )
                )
                && 'Table'[Status] = "Down"
        )
    )
)

         

         


        Best regards,