Forum Discussion
Filtering table for rows showing the max date per group
Hi all,
I have a table that is arranged somewhat like this:
| RowID | ConsumerID | VisitDate |
| 1 | 1111 | 4/1/2023 |
| 2 | 2222 | 4/1/2023 |
| 3 | 1111 | 3/9/2023 |
| 4 | 3333 | 5/1/2023 |
| 5 | 2222 | 3/8/2023 |
| 6 | 3333 | 3/9/2023 |
| 7 | 1111 | 3/8/2023 |
| 8 | 1111 | 2/14/2023 |
| 9 | 3333 | 1/28/2023 |
| 10 | 2222 | 3/31/2023 |
I'm trying to filter for the most recent VisitDate for each ConsumerID so that there will be 1 row per ConsumerID:
| RowID | ConsumerID | VisitDate |
| 1 | 1111 | 4/1/2023 |
| 2 | 2222 | 4/1/2023 |
| 4 | 3333 | 5/1/2023 |
I feel like I've done this before (successfully) but everything I've tried this time has yielded either errors or unfiltered data.
I've tried using combinations of Filter, groupby, max, maxx, summarize, summarizecolumns, topn, etc. I'm sure this is much simpler than I'm making it. Can someone point me in the right direction?
Ok, you should create a new table (in my case Sheet1_filtered) with a column named consumerID (same name as your current table). You can get the distinct ids via:
Sheet1_filtered = DISTINCT(Sheet1[ConsumerID])You can than calculate the most recent VisitDate for each ConsumerID in a new column.
MostRecentVisitDate = CALCULATE ( MAX (Sheet1[VisitDate]), FILTER ( Sheet1, Sheet1[ConsumerID] = EARLIER ( Sheet1_filtered[ConsumerID] ) ) )Make sure there is a relationship between the two consumerID in both tables (use the model view).
Sheet1 is your current table. Sheet1_filtered will the table that you should create.
Let me know if this works.
Best,
Milan
Cool cool, try in the filtered table:
RelatedRowID = VAR ConsumerID = Sheet1_filtered[ConsumerID] VAR MostRecentVisitDate = Sheet1_filtered[MostRecentVisitDate] RETURN CALCULATE ( MAX( Sheet1[RowID]), Sheet1[ConsumerID] == ConsumerID && Sheet1[VisitDate] == MostRecentVisitDate )Best,
Milan
PS. Make sure to thumbs up and mark as solution if it fits your needs.
6 Replies
- milanpasschier3
Resolver I
Ok, you should create a new table (in my case Sheet1_filtered) with a column named consumerID (same name as your current table). You can get the distinct ids via:
Sheet1_filtered = DISTINCT(Sheet1[ConsumerID])You can than calculate the most recent VisitDate for each ConsumerID in a new column.
MostRecentVisitDate = CALCULATE ( MAX (Sheet1[VisitDate]), FILTER ( Sheet1, Sheet1[ConsumerID] = EARLIER ( Sheet1_filtered[ConsumerID] ) ) )Make sure there is a relationship between the two consumerID in both tables (use the model view).
Sheet1 is your current table. Sheet1_filtered will the table that you should create.
Let me know if this works.
Best,
Milan
Thank you, this is great! Is there a way to also retain the other column(s)? In my example data, it would be RowID column. I tried using LookupValue but since the most recent visit date isn't a unique value, it doesn't work to find its corresponding RowID value. Perhaps I concatenate the ConsumerID and MostRecentVisitID column values in both tables? Is there another way to do it? Thank you so much!
- milanpasschier3
Cool cool, try in the filtered table:
RelatedRowID = VAR ConsumerID = Sheet1_filtered[ConsumerID] VAR MostRecentVisitDate = Sheet1_filtered[MostRecentVisitDate] RETURN CALCULATE ( MAX( Sheet1[RowID]), Sheet1[ConsumerID] == ConsumerID && Sheet1[VisitDate] == MostRecentVisitDate )Best,
Milan
PS. Make sure to thumbs up and mark as solution if it fits your needs.
- milanpasschier3
Hey, just to be sure... Do you want this as a measure or do you want a new table with the filtered data? Best, Milan
Hi there,
Ideally, I'm looking for a filtered table that retains all the columns. Thank you for taking a look at this!
