Calculated Table Using SUMMARIZE to return distinct values by max date

[DonRitchie]

Hi all!  Happy New Year!

Been banging my head on this all morning.  I am creating a table using the following:

EmpTypeTable = SUMMARIZE(Employees,Employees[Employee], Employees[EmployeeType], Employees[Date])

An Employee many have multiple EmployeeType's.  I want to return the EmployeeType with the most recent date.

I have this: 

Employee	EmployeeType	Date
A	PT	1/1/2024
B	PT	12/1/2023
B	FT	12/25/2023
C	FT	11/28/2023
C	PT	12/15/2023

I want this:

Employee	EmployeeType	Date
A	PT	1/1/2024
B	FT	12/25/2023
C	PT	12/15/2023

I have tried GROUPBY, and various FILTER commands, and SUMMARIZETABLE.  I can't figure it out.  I get errors about scalars into scalars.  I could even live with a calculated column that kept the original table and ADDCOLUMN'd the EmployeeType with the max date.

[ThxAlot · Accepted solution]

[Anonymous · Accepted solution]

Hi @DonRitchie 

@ThxAlot Good share!

For your question, here I give you the other method:

Here is the data you provided

“Employees”

First, create a measure to calculate the “max_date”

max_date = 
CALCULATE(MAX('Employees'[Date]), 
FILTER(ALL('Employees'), 'Employees'[Employee ] = MAX('Employees'[Employee ])))

Then, create a new table

result_employees = 
SELECTCOLUMNS(FILTER(ALL('Employees'), 
'Employees'[Date] = [max_date]), 
"employee", 'Employees'[Employee ], "employtype", 'Employees'[EmployeeType ], "date", 'Employees'[Date])

Here is the result

Regards,

Nono Chen

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

[DonRitchie]

This works as well, in the situation where I would want a comparison table.  Thank you!

[ThxAlot · Accepted solution]

[DonRitchie]

Perfect.  Exactly what I was looking for, and the process is easily understandable for when I need to do similar calculations (all the time).  Although, I had to add DEFAULT, between ORDERBY and PARTITIONBY. 

Thank you!