Issue extracting details when filtering by latest date

[Knif]

Hi Guys,

This is my first post, and I'd like to start by thanking you for this forum where I usually find a lot of precious information.

I am fairly new to using PowerBI, and I'm facing a problem that I can't solve, even though I feel I have solved more complicated ones... I feel a bit ridiculuous 👶

I have the following table:

Country	Company	User	Date of Registration
Italy	Coca-Cola	A	12/01/2022
Italy	Pepsi	B	20/10/2022
Italy	Coca-Cola	C	13/07/2021
France	Fanta	D	15/06/2022
Italy	Coca-Cola	E	11/01/2022
France	Kro	F	10/02/2021

And I would like to get a resulting table displaying the Lastest Registration by Country

Country	Company	User	Date of Registration
Italy	Pepsi	B	20/10/2022
France	Fanta	D	15/06/2022

I can easily get the latest Registration Date by Country, but when I insert the user details, then it logically multiples the lines...

I tried using LASTDATE, MAX, etc. but I don't find the solution.

Would you have any hint to share with me ?

Cheers,

Nick

[Knif]

Thank you very much. It works great. ❤️
I would have never figured it out by myself.

[johnt75 · Accepted solution]

If you want the results to be dynamic and respond to filters and slicers you would need to create a separate measure for each column you want to display. The general format could be

Latest Country =
SELECTCOLUMNS (
    SAMPLE ( 1, 'Table', 'Table'[Registration Date], DESC ),
    'Table'[Country ID]
)

and just swap the final argument to SELECTCOLUMNS for the appropriate column you wish to return.

I use SAMPLE instead of TOPN because SAMPLE is guaranteed to return only 1 row, even in the event of ties, whereas TOPN would return all the rows in the event of a tie.