cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StefanM Regular Visitor
Regular Visitor

Filter ID to most recent Date / Time / Timezone

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: 123Data Type: Date / Time / Timzone
2225/05/2019 01:03:28 +00:00
2325/05/2019 02:03:28 +00:00
2325/05/2019 03:03:28 +00:00
2425/05/2019 04:03:28 +00:00
2525/05/2019 05:03:28 +00:00
2625/05/2019 06:03:28 +00:00
2625/05/2019 07:03:28 +00:00
2625/05/2019 08:03:28 +00:00
2625/05/2019 09:03:28 +00:00

 

And this is what I want to end up with:

 

Data Type: 123Data Type: Date / Time / Timzone
2225/05/2019 01:03:28 +00:00
2325/05/2019 03:03:28 +00:00
2425/05/2019 04:03:28 +00:00
2525/05/2019 05:03:28 +00:00
2625/05/2019 09:03:28 +00:00


Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter ID to most recent Date / Time / Timezone

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.

Untitled.png

Hope this can help you.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Highlighted
kcantor Super Contributor
Super Contributor

Re: Filter ID to most recent Date / Time / Timezone

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Community Support Team
Community Support Team

Re: Filter ID to most recent Date / Time / Timezone

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.

Untitled.png

Hope this can help you.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 299 members 3,815 guests
Please welcome our newest community members: