How to return distinct text values based on earliest date and Id columns?

[Anonymous]

Hi Power Bi Community,

I am so glad to find such a big community that are willing to help others!

Could you please help me out! 

To simplify things, I have 'Fruit' visual table with the following: 

ID	Fruit Selected	Date Selected	Time Selected
1	Banana	15/3/2022	10:41pm
1	Banana	15/3/2022	11:41pm
1	Apple	15/3/2022	11:41pm
2	Banana	15/3/2022	9:10pm
2	Apple	15/3/2022	10:10pm

I want the table to be able to return me the below instead:

ID	Fruit Selected	Date Selected	Time Selected
1	Banana	15/3/2022	10:41pm
1	Apple	15/3/2022	11:41pm
2	Banana	15/3/2022	9:10pm
2	Apple	15/3/2022	10:10pm

See how it filterd out the duplicate banana that was selected on later time for the same ID.

I want a DAX formula/measure that able to perform this for me.

Please note, the measure must be able to work in a DirectQuery mode.

Thank you!!!!

[johnt75 · Accepted solution]

First Time = SELECTCOLUMNS( TOPN( 1,'Table', 'Table'[Time Selected],ASC), "@time", 'Table'[Time Selected] )

create this as a measure and put it in a table visual with the ID, fruit and date columns

[Anonymous]

Hi Johnt75,

Thank you for your support.

I have created the above measure but it return date and time in one column instead, it did not eliminate the duplicate text values. 😥

[johnt75]

Make sure that the selected date and selected time columns are separate columns and that they have the correct types - the selected date column should be a date type, not a datetime, and the selected time column should be a time type.

You may also need to change the display format of the measure to just show the time

[Anonymous]

You are a legend! Thank you so much! You have saved me. 🤗