Forum Discussion
Find first values per ID and earliest Date
Hi All,
I am attempting to retrieve the earliest record for several data points within my table per ID. The bolded lines in the data below is what information I am interested in. I want to put this in to a table. When I do this with the current data set and I add values it shows the First or Last per value. The ID column is in the row section and Name/Company/etc is in the values section. "First" and "Last" in this context appears to be alphabetical. I want the first based on the DOS column.
I have seen a few other posts discussing adding a calculated column and using Minx to find that information but so far I haven't been able to get that to work correctly.
Example of data
| ID | Name1 | Name2 | Company | DOS |
| 555123 | Smith, Richard | Doe, John | Arizona | 5/1/2022 |
| 555123 | Smith, Richard | Name, Mary | New Mexico | 3/22/2023 |
| 223441 | Denis, Rodgers | Red, John | Arizona | 1/5/2021 |
| 223441 | Denis, Rodgers | Abdulla, Dennis | Arizona | 1/10/2022 |
| 333422 | Johson, Sam | Riley, Ricardo | Texas | 1/3/2021 |
| 333422 | Johson, Sam | Funk, Jim | Arizona | 1/22/2021 |
| 333422 | Johson, Sam | Thompson, Tonya | Colorado | 9/3/2022 |
The rows outside of the bolded rows I would not be intersted in showing at all on the table. I want the final table to look like below.
| ID | Name1 | Name2 | Company | DOS |
| 555123 | Smith, Richard | Doe, John | Arizona | 5/1/2022 |
| 223441 | Denis, Rodgers | Red, John | Arizona | 1/5/2021 |
| 333422 | Johson, Sam | Riley, Ricardo | Texas | 1/3/2021 |
I think I need a calculated column or measure for each data point (Name1, Name2, etc) based on the EARLIEST DOS by each Unique ID.
Any help to point me in the right direction would be greatly appreciated.
Thanks,
Hi An_dy
To perform the filter you are interested in.
In the first step, you can add a ranking column to your data table.
In order of date, rank each ID
DAx formula ;Ranking = RANKX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])),'Table'[DOS],,ASC)You filter the table by the column you added when you display it. The value of 1
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- Ritaf1983
Super User
Hi An_dy
To perform the filter you are interested in.
In the first step, you can add a ranking column to your data table.
In order of date, rank each ID
DAx formula ;Ranking = RANKX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])),'Table'[DOS],,ASC)You filter the table by the column you added when you display it. The value of 1
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
