Forum Discussion
Calculated Table Using SUMMARIZE to return distinct values by max date
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.
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.
4 Replies
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!
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.
This works as well, in the situation where I would want a comparison table. Thank you!
