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.