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...
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,

     

  • 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,