Filter ID to most recent Date / Time / Timezone

[StefanM]

Hello everyone! Im having some difficulty filtering one of my datasets.

Basically, I have a table with a bunch of non-unique IDs with various dates associated with them.
I want to make the IDs unique, retaining only the most recent Date / Time / Timezone row.

To help explain a little more, this is what I have atm:

Data Type: 123	Data Type: Date / Time / Timzone
22	25/05/2019 01:03:28 +00:00
23	25/05/2019 02:03:28 +00:00
23	25/05/2019 03:03:28 +00:00
24	25/05/2019 04:03:28 +00:00
25	25/05/2019 05:03:28 +00:00
26	25/05/2019 06:03:28 +00:00
26	25/05/2019 07:03:28 +00:00
26	25/05/2019 08:03:28 +00:00
26	25/05/2019 09:03:28 +00:00

And this is what I want to end up with:

Data Type: 123	Data Type: Date / Time / Timzone
22	25/05/2019 01:03:28 +00:00
23	25/05/2019 03:03:28 +00:00
24	25/05/2019 04:03:28 +00:00
25	25/05/2019 05:03:28 +00:00
26	25/05/2019 09:03:28 +00:00

Any ideas?

[v-piga-msft · Accepted solution]

Hi @StefanM ,

By my tests, there are two ways for us to achieve your desired output.

1. Create the dax measure with the formula below.

Measure =
CALCULATE (
    MAX ( 'Table'[Data Type: Date / Time] ),
    ALLEXCEPT ( 'Table', 'Table'[Data Type: 123] )
)

2. Change the Field Data Type: Date / Time to be Latest like below.

Hope this can help you.

Best Regards,

Cherry

[kcantor]

@StefanM 

When I need to filter to the last date/time, I usually complete that within Power Query during the ETL process using the Group by function.

In Query editor, group IDs by Max Date and Time and that should clean up the information for you. 

If you need to maintain the entire list as well, reference the original query ( to save memory and load time as your queries grow) and perform the grouping on that particular query. The IDs then become unique and can  be used to filter your original query.