Referencing rows within same table based on events

[Smallegrue]

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 ID	Date	Equipment	Shift	Time	Duration	Status
1	2020-02-17	T101	Night	0:00	43200	Up
2	2020-02-18	T101	Day	0:00	21600	Up
3	2020-02-18	T101	Day	6:00	10800	Down
4	2020-02-18	T101	Day	9:00	10800	Up
5	2020-02-18	T101	Night	0:00	43200	Down
6	2020-02-19	T101	Day	0:00	43200	Down
7	2020-02-19	T101	Night	0:00	43200	Down
8	2020-02-20	T101	Day	0:00	43200	Down
9	2020-02-20	T101	Night	0:00	43200	Down

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 ID	Date	Equipment	Shift	Time	Duration	Status	Original Failure line ID
1	2020-02-17	T101	Night	0:00	43200	Up
2	2020-02-18	T101	Day	0:00	21600	Up
3	2020-02-18	T101	Day	6:00	10800	Down	3
4	2020-02-18	T101	Day	9:00	10800	Up
5	2020-02-18	T101	Night	0:00	43200	Down	5
6	2020-02-19	T101	Day	0:00	43200	Down	5
7	2020-02-19	T101	Night	0:00	43200	Down	5
8	2020-02-20	T101	Day	0:00	43200	Down	5
9	2020-02-20	T101	Night	0:00	43200	Down	5

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

Thank you,

Steph

[Greg_Deckler]

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[Smallegrue]

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 ID	Date	Equipment	Shift	Time	Duration	Status	Original Failure line ID
1	2020-02-17	T101	Night	0:00	43200	Up
2	2020-02-18	T101	Day	0:00	21600	Up
3	2020-02-18	T101	Day	6:00	10800	Down	3
4	2020-02-18	T101	Day	9:00	10800	Up
5	2020-02-18	T101	Night	0:00	43200	Down	3
6	2020-02-19	T101	Day	0:00	43200	Down	3
7	2020-02-19	T101	Night	0:00	43200	Down	3
8	2020-02-20	T101	Day	0:00	43200	Down	3
9	2020-02-20	T101	Night	0:00	43200	Down	3

Any help is appreciated

[v-lid-msft · Accepted solution]

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]

Thanks Greg,

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