Filtering table for rows showing the max date per group

[TJohnson6754]

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?

[milanpasschier3 · Accepted solution]

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

[milanpasschier3 · Accepted solution]

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

[TJohnson6754]

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 · Accepted solution]

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.

[TJohnson6754]

This is perfect, thank you so much!!

[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

[TJohnson6754]

Hi there,

Ideally, I'm looking for a filtered table that retains all the columns. Thank you for taking a look at this!