Forum Discussion
How to select only first value
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