So I'm looking at aggregating some employee data, but I'm running into an issue. In my example table below, I have listed the Employee ID, Employee KEY (we use encrypted SSN), Hire Date, Termination Date, and Is_Active (Y or N). As you can see Employee A and D is the same person (same employee key), but has two different Employee IDs. They were assigned a new Employee ID upon rehire, but I need to aggregate any data from their previous employement with data from their current employment.
EmployeeID Employee Key Hire Date Termination Date Is_Active
A AAB 2/1/2019 4/15/2019 N
B BBB 3/1/2019 null Y
C CCC 4/1/2019 null Y
D AAB 5/1/2019 null Y
I assume I need a new calucated column that looks for any instance of Is_Active = Y with the associated Employee Key, and that calculated column will give me a new "Y" or "N" depending if there is any occurrence of that Employee Key showing Is_Active = Y.
So basically I'm looking for this....
EmployeeID Employee Key Hire Date Termination Date Is_Active Active Employee
A AAB 2/1/2019 4/15/2019 N Y
B BBB 3/1/2019 null Y Y
C CCC 4/1/2019 null Y Y
D AAB 5/1/2019 null Y Y
I just can't figure out the DAX forumla to get there.
Wow, I thought this would be an easy answer. I figured I'd just use SELECTEDVALUE instead of the CONTAINS expression, and add another condition to the filter to pick the max hire date.
Boy was I wrong. Just using [Hire Date] = MAX([Hire Date]) only gives correct results when the employee's hire date was the maximum in the whole table. Due to circular dependency restrictions when creating a column, it took me 2 hours of messing with the DAX to come up with this expression that works:
Current Employee ID =
VAR cur_key = Employees[Employee Key]
TOPN(1,FILTER(ALL(Employees),cur_key=Employees[Employee Key]),Employees[Hire Date],DESC))
It's a complete mess, but this was the only way I found to first filter by Employee Key, and while keeping that filter, also get only the row with the MAX hire date.
I feel like there's a cleaner way to get here with SUMMARIZE or maybe KEEPFILTERS, but I've put in my time on this. I'm done.