If any occurrence in column from grouping another column

[benjaminlperry]

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.

[Cmcmahan]

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])) 
)

[benjaminlperry]

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

[Cmcmahan]

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.