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

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,