Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
So if you've set up Is_Active as a calculated column, this should be easy enough.
Active Employee =
CALCULATE(
CONTAINS( Employees, Employees[Is_Active], "Y"),
FILTER(ALL(Employees), Employees[Employee Key] = EARLIER(Employees[Employee Key]))
)
Thanks! That worked. Now what if I want an additional column that shows the employee's most recent employeeID. So it would look like.
Current Employee ID
D
B
C
D
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] RETURN CALCULATE(SELECTEDVALUE(Employees[Employee ID]), 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.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |