Forum Discussion

TJohnson6754's avatar
TJohnson6754
Frequent Visitor
3 years ago
Solved

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/20...
"FILTER"
  • milanpasschier3's avatar
    milanpasschier3
    3 years ago

    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's avatar
    milanpasschier3
    3 years ago

    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's avatar
milanpasschier3
Icon for Resolver I rankResolver I
3 years ago

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's avatar
    TJohnson6754
    Frequent Visitor
    3 years ago

    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's avatar
      milanpasschier3
      Icon for Resolver I rankResolver I
      3 years ago

      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's avatar
        TJohnson6754
        Frequent Visitor
        3 years ago

        This is perfect, thank you so much!!