The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
Solved! Go to Solution.
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LearnAndPractise(Everyday) ) |
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
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
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!
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!
User | Count |
---|---|
26 | |
10 | |
8 | |
6 | |
6 |
User | Count |
---|---|
32 | |
14 | |
10 | |
10 | |
9 |