Filter based on most recent value but with another criteria

[james_bruton]

Hi. I’m struggling with filtering the above table in a clean way.

I would like to make a filter so that you can only see the most recent review dates (each row is a review) for each person.

I would like to end up with a filtered list which is one row per person and only the one which includes the planned next review furthest into the future

Hope that makes sense. Any pointers would be great. Coda is awesome but a lot to take in at once!

[Pch]

Hi @james_bruton and Welcome to the Community !

Have you tried to filter your table using something like :

thisRow.[Date of review] = [Upcoming reviews].[Date of review].Max()

This compares the value in thisRow.[Date of review] to the list of all dates in your field [Date of review] in your table [Upcoming reviews] from which we only kept the maximum value (.Max())

Using MaxBy() could work too :

[Upcoming reviews].[Date of review].MaxBy(CurrentValue) = thisRow.[Date of review]

Where [Upcoming reviews].[Date of review] is once again the list of all your dates in the field [Date of review] (each date in the list being stored as a CurrentValue). This should return (behind the scene) the maximum value in the list of dates which is then compared to the value in thisRow.[Date of review]

There might be other ways to get to that result though .

I hope this helps

[james_bruton]

Thanks very much for such a prompt bit of help. I think that’s part of the way there. I couldn’t get the bottom formula to work (though I haven’t played with it yet). The top one returns the most recent review but only for one of the people :

What I’m trying to achieve is a formula which returns that row as well as the john Smithee single entry

Does that make more sense?

Thanks again

[Pch]

@james_bruton

Does that make more sense?

Yes, sorry … I misread your question earlier .

If I get this right this time, you want to have your table filtered so that you get the max value within your field [Date of review] by person in your table .

I think the easiest way to get there would be to add a checkbox field to determine which rows needs to be kept (the Checkbox field can be hidden later) and then filter your table by the value within the checkbox

The formula I used for the Checkbox field ([Max by User]) in my test doc is this one :

[Table 2].Filter(
  People = thisRow.People
).[Date of review].Max() = thisRow.[Date of review]

So, it takes the table [Table 2] and look for rows where CurrentValue.People = thisRow.People which returns a list of rows behind the scene.

From that list of rows, I get the dates in the field [Date of review] and only keep the max value (using Max()).
And, for each of the row in the table [Table 2] this maximum value is then compared to the value in thisRow.[Date of review] .
If both values are equals, the formula returns true and false if not

Then in the view of my table, I only needed to add a simple filter such as : [Max by User] → is equal to → Checked

Or another possibility would be to use a single formula as a filter for your view, such as :

Table.People.Unique().ForEach(
  CurrentValue.WithName(U,
    Table.Filter(People = U).[Date of review].Max()
   )
).Contains(thisRow.[Date of review])

Where :

• Table.People.Unique() is the list of all the People in the table from which I only keep the unique values (Unique())
• Then, ForEach() person in that list (each person being stored as CurrentValue) I give to their CurrentValue the name U using WithName().
• Once that’s done, I can again compare CurrentValue.People in my table to each person (U) and get the maximum value from the list of [Date of review]

This filter, so far, returns a list of 2 dates (because there are only 2 users in my test doc) and all that that’s left is to compare the values in the that list to the value in thisRow.[Date of review] ([ ... ].Contains(thisRow.[Date of review]))

I’m not sure if everything is understandable as apparently I still don’t have enough coffee in my system yet ( ) but don’t hesitate if you have questions .

[james_bruton]

Amazing support. thank you ever so much. I’ve just had an unpleasant trip to the dentist. Cheered me up no end to have an answer ready when I got back!

I went with the second one in the end (as it was simpler for my needs. Here is my document with it in for reference to anyone else:

[Pch]

No problem @james_bruton !

I’m very glad to know that it helped and worked for your use case !