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