Forum Discussion
Earliest Value based on filter/criteria
Hi,
I have a dataset as follows:
| ID | Date | Success | Score |
| 123 | 11/19/2020 | 0 | 5 |
| 123 | 11/20/2020 | 1 | 6 |
| 123 | 11/21/2020 | 1 | 2 |
| 122 | 11/19/2020 | 0 | 8 |
| 122 | 11/20/2020 | 1 | 9 |
| 133 | 11/19/2020 | 1 | 1 |
| 133 | 11/20/2020 | 1 | 3 |
| 144 | 11/19/2020 | 0 | 4 |
You'll notice that ID is a column with repeats with each row tracking the date and success and score.
What I'm looking for is to be able to return the earliest known score for each unique ID where success is = 1. I would like to be able to populate the following (earliest_success_score).
| ID | Date | Success | Score | Earliest_success_score |
| 123 | 11/19/2020 | 0 | 5 | null |
| 123 | 11/20/2020 | 1 | 6 | 6 |
| 123 | 11/21/2020 | 1 | 2 | null |
| 122 | 11/19/2020 | 0 | 8 | null |
| 122 | 11/20/2020 | 1 | 9 | 9 |
| 133 | 11/19/2020 | 1 | 1 | 1 |
| 133 | 11/20/2020 | 1 | 3 | null |
| 144 | 11/19/2020 | 0 | 4 | null |
Thanks!
p.s. it's possible that there could be multiple submissions for a single day and the data has a time stamp in additiona to the date so the solution would look at earlist date and time (keeping the sample data simpler for ease of my understanding).
Anonymous , Create a new column like
Column = var _1 = minx(FILTER(Data, [ID] =EARLIER([ID]) && Data[Success] = 1),Data[Date]) return if(_1 =[date] ,minx(FILTER(Data, [ID] =EARLIER([ID]) && Data[Success] = 1 && [Date] =_1),[Score]),BLANK())
2 Replies
- amitchandak
Super User
Anonymous , Create a new column like
Column = var _1 = minx(FILTER(Data, [ID] =EARLIER([ID]) && Data[Success] = 1),Data[Date]) return if(_1 =[date] ,minx(FILTER(Data, [ID] =EARLIER([ID]) && Data[Success] = 1 && [Date] =_1),[Score]),BLANK())thanks amitchandak ! your solution worked great.