Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
benjaminlperry
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
Cmcmahan
Resident Rockstar
Resident Rockstar

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.