Summerise table based on UserID and Hiredate

[nick9one1]

I have a table of employees that lists all the roles employees have held within the company. I need UserID to be unique. Some employees have changed roles so the UserID is duplicated in multiple rows. 

I need to filter, or create a new table that has unique userId's, and takes the row that has the highest Hire_Date for each User ID.

eg the current table is like this;

User1	01/01/2020
User1	01/01/2022
User2	01/01/2022

 

I need it to look like this;

User1	01/01/2022
User2	01/01/2022

Can anyone suggest how to do this? Thanks

[v-yiruan-msft · Accepted solution]

Hi @nick9one1 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Method 1: Using the aggregation Latest

Using the aggregation function Latest

Method 2: Create a calculated table

Table 2 =
SUMMARIZE (
    'Table',
    'Table'[UserID],
    "Max Hire Date", CALCULATE ( MAX ( 'Table'[Hire_Date] ) )
)

Create a calculated table

Best Regards

[Fowmy]

@nick9one1 
Create your new table as follows:

New Table:
ADDCOLUMNS(
	DISTINCT( Table[UserID] ),
	"Latest Hire Date" , CALCULATE( MAX( Table[Hire_Date ] ) )
)