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.
I need help with a logic / measure to distinctly count breakdown events in my data. snip 1 my data sample and snip 2 is expected results.
i want a distinct count of downs based on ID. In the data attached, EO759 is contineously down from August 10-August 12th. Note that start and end is contineous. Example close for EO759 on 10th day shift is the start of EO759 on 10th night shift , hence is a single count. EO759 down from 10th to 12 will be counted as 1, because is the same down event that has taken a couple of days to get fixed.
EO759 is down again on August 14th. this breakdowns will be counted twice because there are seperate event based on the start and end time.
Expected results
Thanks for the help.
// Here's how to do it in DAX.
// For each record (with the same ID) try to
// establish if there is another record where
// ID is the same and the start date equals
// the end date of the record in question.
// If there is one, then you've got a continuity,
// if there isn't, you've found the end of
// a continuity. Then you just count the number
// of discontinuities. Here's the
// implementation in DAX (but it would be much
// better to do it in PQ). T is your table as
// you've shown it.
[Expected Results] = // calculated table
groupby(
ADDCOLUMNS(
T,
"@NextShiftDoesntExist",
var __id = T[ID]
var __end = T[End]
return
// If this is 1,
// we've got a discontinuity.
1 * ISEMPTY(
FILTER(
T,
T[ID] = __id
&&
T[Start] = __end
)
)
),
T[ID],
"Count", SUMX( CURRENTGROUP(), [@NextShiftDoesntExist] )
)
Please bear in mind that the End and Start fields should have the datetime data type. No text.
@Selded The way you have that written it is returning a table. So you need to have that formula pasted in after selecting to create a new table.
@Selded - 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....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
__Current - __Previous
I am pretty certain that you will need an Index and to create a "Continuous" column that returns 1 if it is continuous with the previous row.
Please assist with how i can create an index and a contineous column that returns 1 for equipments on long term breakdown and also how i can calculate the MTTR.
@Selded - Can you add an index column in Power Query? Sorry, can you provide an example of what you are looking for, not sure I have a clear understanding.
I want to count contineous breakdowns as 1 as shown in my snips above . The count for EO759 is 3 because the contineous downtimes even was counted as 1.
i also need hep with a measure for the MTTR .
Thanks for the help
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |