topic Re: Filtering table for rows showing the max date per group in DAX Commands and Tips

Filtering table for rows showing the max date per group

TJohnson6754 — Wed, 03 May 2023 15:21:00 GMT

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?

Re: Filtering table for rows showing the max date per group

milanpasschier3 — Wed, 03 May 2023 15:40:50 GMT

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

Re: Filtering table for rows showing the max date per group

TJohnson6754 — Wed, 03 May 2023 16:11:10 GMT

Hi there,

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

Re: Filtering table for rows showing the max date per group

milanpasschier3 — Wed, 03 May 2023 16:35:38 GMT

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

Re: Filtering table for rows showing the max date per group

TJohnson6754 — Wed, 03 May 2023 16:53:14 GMT

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!

Re: Filtering table for rows showing the max date per group

milanpasschier3 — Wed, 03 May 2023 17:17:26 GMT

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.

Re: Filtering table for rows showing the max date per group

TJohnson6754 — Wed, 03 May 2023 23:23:26 GMT

This is perfect, thank you so much!!