topic Re: How to return distinct text values based on earliest date and Id columns? in DAX Commands and Tips

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

Anonymous — Tue, 15 Mar 2022 12:06:35 GMT

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!!!!

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

johnt75 — Tue, 15 Mar 2022 12:12:16 GMT

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

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

Anonymous — Tue, 15 Mar 2022 12:22:02 GMT

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. 😥

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

johnt75 — Tue, 15 Mar 2022 12:27:14 GMT

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

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

Anonymous — Tue, 15 Mar 2022 12:48:24 GMT

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