Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
neilmc
Frequent Visitor

Filter rows by max date keeping ID

Hello I have the data below

 

Ideally I would like to select for each PerID the containing the latest date.  I've tried groupby in query editor (groupby perID then select Max date and all rows, however when I expand the table to get the DBID field I end up with duplicates

 

Any pointers gratefully recieved

 

Cheers

Neil

 

PerIDDBIDStartDate
PER1000733918908 
PER100073392855002/08/2010 23:00
PER100223390044609/05/2017 00:00
PER1002233918909 
PER1002233923009 
PER100223392855106/11/2007 00:00
PER100293387368206/07/2020 23:00
PER100293389722401/02/2023 00:00
PER100293390044703/10/2018 00:00
PER100293390044808/10/2018 00:00
PER100293390044918/10/2018 00:00
PER100293390045001/11/2018 00:00
PER100293390045105/04/2019 00:00
PER100293390045222/07/2019 00:00
PER100293390045313/08/2019 00:00
PER100293391086015/09/2020 00:00
PER100293391261006/02/2023 00:00
PER100293391261112/04/2023 00:00
PER100293391744812/04/2023 00:00
1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

Hi @neilmc ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a table.

vtangjiemsft_0-1689044843987.png

Table 2 = 
var _a=ADDCOLUMNS('Table',"max", CALCULATE(MAX('Table'[StartDate]),FILTER('Table','Table'[PerID]=EARLIER('Table'[PerID]))))
var _b= FILTER(_a,[StartDate]=[max]) 
return SELECTCOLUMNS(_b,"PerID",[PerID],"DBID",[DBID],"StartDate",[StartDate])

(3) Then the result is as follows.

vtangjiemsft_1-1689044893227.png

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

 

View solution in original post

3 REPLIES 3
v-tangjie-msft
Community Support
Community Support

Hi @neilmc ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a table.

vtangjiemsft_0-1689044843987.png

Table 2 = 
var _a=ADDCOLUMNS('Table',"max", CALCULATE(MAX('Table'[StartDate]),FILTER('Table','Table'[PerID]=EARLIER('Table'[PerID]))))
var _b= FILTER(_a,[StartDate]=[max]) 
return SELECTCOLUMNS(_b,"PerID",[PerID],"DBID",[DBID],"StartDate",[StartDate])

(3) Then the result is as follows.

vtangjiemsft_1-1689044893227.png

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

 

neilmc
Frequent Visitor

Hello - I think I was being I bit of an idiot!  It looks like the grouping method worked in power query but the issue was that I had duplicate person ids with exactly the same start date

Ahmedx
Super User
Super User

can you show the result you expect

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.