Forum Discussion
Filtering table for rows showing the max date per group
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.
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!
Resolver ICool 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.
This is perfect, thank you so much!!