How to select only first value

[konradjonsson]

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.

[JosefPrakljacic]

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] )

)

)

RETURN

CALCULATE (

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 🙂

[konradjonsson]

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

)

[konradjonsson]

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

[amitchandak]

check this

https://community.powerbi.com/t5/Desktop/Find-value-for-first-and-last-date-by-person/m-p/62911#M259...