Slice by latest date

[PatM]

Hello,

Trying to create a slice that is filtered based on latest date. Example given below where the golfer with the latest date is returned (i.e. ID 3 is removed from the slice)

Data

ID	GolferID	Type	Hcap	Date
1	1	GUI	16	29/06/2020
2	2	GUI	12	29/06/2020
3	3	GUI	18	29/06/2020
4	3	GUI	17	16/07/2020

Slice

ID	GolferID	Type	Hcap	Date
1	1	GUI	16	29/06/2020
2	2	GUI	12	29/06/2020
4	3	GUI	17	16/07/2020

Thanks for any help,
Pat

[Steve]

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

How do I get the last row of this thing only? MAXROW( "My Table", "_ROWNUMBER", ([_THISROW].[Thing] = [Thing]) ) Replace My Table with the name of the table whose row you want; and Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID).

[PatM]

Thanks Steve, tried a few examples to get this to work but failing miserably

LOOKUP( MAX( SELECT( HcapGUI[_ROWNUMBER], ([_THISROW].[Date] = [Date]) ) ), “HcapGUI”, “_ROWNUMBER”, “HcapID” )

The expression is valid but its result type ‘Ref’ is not one of the expected types: Yes/No

If I wasn’t clear, I’m trying to get multiple rows returned not just one row.

[hru · Accepted solution]

For a slice, the expression is something like:

[_ROWNUMBER] =
	MAX(
	  SELECT(
		HcapGUI[_ROWNUMBER],
        ([GolferID] = [_THISROW].[GolferID])
     )
    )

to get the Yes/No expected type.

[PatM]

Thank you very much. Worked first time