Forum Discussion
How to select only first value
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.
4 Replies
How about adding a calculated column like this? (I named the table "example" and the column "test")
Test =VAR FirstSerialDate =CALCULATE (FIRSTDATE ( Example[Service_Date] ),FILTER (ALL ( Example ),Example[Serial number] = EARLIER ( Example[Serial number] )))RETURNCALCULATE (MIN ( Example[Failure time (days)] ),Example[Service_Date] = FirstSerialDate)If that answer helped may I ask you to mark it as solution and share some kudos :-)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
