cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
benjaminlperry Frequent Visitor
Frequent Visitor

If any occurrence in column from grouping another column

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.

3 REPLIES 3
Super User
Super User

Re: If any occurrence in column from grouping another column

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 Frequent Visitor
Frequent Visitor

Re: If any occurrence in column from grouping another column

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

 

 

Super User
Super User

Re: If any occurrence in column from grouping another column

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.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,391)