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 :
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
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
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 →
Or another possibility would be to use a single formula as a filter for your view, such as :
Table.Filter(People = U).[Date of review].Max()
).Contains(thisRow.[Date of review])
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 .