Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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,
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...
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...
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
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,
Thanks Greg,
I'll take a look at it! I'll let you know.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |