Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
98 | |
78 | |
64 | |
56 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |