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 have a table that include the following columns (except the 'Days between failure', which is part of another question).
Type | Serial number | Sold_Date | Service_Date | Failure time (days) | Days between failure | Product |
Sales | 1001 | 2010-02-17 00:00 | X | |||
Sales | 1002 | 2010-05-28 00:00 | X | |||
Service | 1002 | 2010-05-28 00:00 | 2013-08-12 00:00 | 1172 | 735 | X |
Service | 1002 | 2010-05-28 00:00 | 2011-01-19 00:00 | 236 | 236 | X |
Service | 1002 | 2010-05-28 00:00 | 2011-05-02 00:00 | 339 | 103 | X |
Service | 1002 | 2010-05-28 00:00 | 2011-08-08 00:00 | 437 | 98 | X |
Sales | 1003 | 2010-01-26 00:00 | X | |||
Service | 1003 | 2010-01-26 00:00 | 2010-07-08 00:00 | 163 | 163 | X |
Service | 1003 | 2010-01-26 00:00 | 2012-09-06 00:00 | 954 | 791 | X |
MTTF | 199,5 | |||||
MTBF | 354,3 |
I want to calculate two different measures - Mean Time To Failure (MTTF) and Mean Time Between Failures (MTBF).
In order to calculate MTTF, I only want to include rows with no failure (e.g. first row above), and rows with the first failure for each Serial number (e.g. row four and row eight above).
In the example, MTTF should be 199.5 (=average(236;163))
My question is - how to extract only the first/earliest service occasion of each unique serial number?
(my own thinking is that I need to create a separate table for this purpose. The original table is used for other purposes, where the sorting order is different)
Regards.
How about adding a calculated column like this? (I named the table "example" and the column "test")
Hi.
Thank you for the suggestion. I tried it (see formula below) on my dataset. I do however get an error message on the formula.
“A circular dependency was detected: Example[Column],Example[Units with Warranty], Example[Column.”
The column ‘Units with Warranty’ is a calculated column I made to count the number of units that have had a warranty case.
Units with Warranty = CALCULATE(
DISTINCTCOUNT('Example'[Serial_Number]);
('Example'[Type]="Warranty")
)
The reference to ’Example[Column] I assume is generic – there is no column in my dataset with the name ‘Column’.
THE FORMULA
FirstValueOnly =
VAR FirstSerialDate =
CALCULATE (
FIRSTDATE ( Example[Service_Date] );
FILTER (
ALL ( Example );
Example[Serial_Number] = EARLIER ( Example[Serial_Number] )
)
)
RETURN
CALCULATE (
MIN ( Example[Replacement_Day] );
Example[Service_Date] = FirstSerialDate
)
Hi. I have not been able to find a solution to my problem with any of the suggestions provided so far. That could very well be due to my own shortcomings. I will make another try to explain what I want.
The table below include several warranty transactions for a specific serial number. For my calculations, I only want to keep the first warranty instance. In other words, all the red marked rows should be excluded.
I know how to do this in Excel (sort on serial number, then on warranty date, then chose Data/Remove duplicates).
=>how do I do this in Power BI?
I do not want to change the original table.
Type | Serial_Number | Sold date | Warranty date | Replacement_Day |
New sale | 101 | 2017-10-25 00:00 | ||
New sale | 102 | 2017-10-25 00:00 | ||
New sale | 104 | 2017-10-25 00:00 | ||
Warranty | 104 | 2017-10-25 00:00 | 2018-01-04 00:00 | 71 |
New sale | 105 | 2017-10-26 00:00 | ||
New sale | 106 | 2017-10-26 00:00 | ||
New sale | 107 | 2017-10-26 00:00 | ||
Warranty | 107 | 2017-10-26 00:00 | 2018-05-02 00:00 | 188 |
Warranty | 107 | 2017-10-26 00:00 | 2018-09-12 00:00 | 321 |
New sale | 108 | 2017-10-27 00:00 | ||
New sale | 109 | 2017-10-31 00:00 | ||
New sale | 110 | 2017-10-30 00:00 | ||
New sale | 111 | 2017-11-01 00:00 | ||
Warranty | 111 | 2017-11-01 00:00 | 2018-02-20 00:00 | 111 |
New sale | 112 | 2017-11-02 00:00 | ||
Warranty | 112 | 2017-11-02 00:00 | 2018-01-05 00:00 | 64 |
Warranty | 112 | 2017-11-02 00:00 | 2018-01-16 00:00 | 75 |
Warranty | 112 | 2017-11-02 00:00 | 2018-01-18 00:00 | 77 |
New sale | 113 | 2017-11-06 00:00 | ||
Average ALL | 129,6 | |||
Average FIRST INSTANCE | 108,5 |
check this
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |