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

Create a manager table using records from an employee table filtering on Manager column

I have a dimension table of Employees with the following columns:

Full Name

Job Title

Reports To

 

I am trying to create a new Calculated Table using the existing Employee table that lists only the managers (Reports To) of employees with the word "Support" in their job title.

 

For example, if this were the Employee table:

 

[Full Name],[Job Title],[Reports To]

John Doe, Support Analyst, Wiley Coyote

Sally Fields, Account Representative, Big Bird

Bob Builder, Sr. Support Engineer, Larry King

Steve Nash, Support Analyst, Wiley Coyote

Jamie Smith, Sr. Support Engineer, Larry King

Wiley Coyote, Manager, Brian Jeffers

Big Bird, Support Supervisor, Sarah Lee

Larry King, Director of Service, Brian Jeffers

Sarah Lee, Director of Service, Brian Jeffers

 

How would I create a new table listing only the managers who have direct reports with the word "Support" in their job title:

 

Wiley Coyote, Manager, Brian Jeffers

Larry King, Director of Service, Brian Jeffers

Sarah Lee, Director of Service, Brian Jeffers

 

I'm able to filter the new table so that only employees with the word "Support" in their Job Title appear, but I'm having trouble figuring out how to filter out the managers based upon the values from "Reports To".

 

dSupportManagers = CALCULATETABLE(dEmployees,FIND("support",LOWER(dEmployees[Job Title]),1,0)>0)

Thank you!

 

Doug

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

This is my pathetic work around. I'm sure there is a much more elegant solution.

 

1. Create a lookup table called dSupportManagerNames

 

 

dSupportManagerNames = CALCULATETABLE(SUMMARIZE(FILTER(tblEmployees,FIND("support",LOWER(tblEmployees[Job Title]),1,0)>0),tblEmployees[Reports_To]))

 

2, Add a calculated column to tblEmployees indicating whether or not the employee is a Support Manager

 

RSC Manager = IF(ISBLANK(LOOKUPVALUE(dSupportManagerNames[Reports_To],dSupportManagerNames[Reports_To],tblEmployees[Full_Name])),FALSE,True)

3. Create a new table called dSupportEmployees

 

dSupportEmployees = CALCULATETABLE(tblEmployees,FILTER(tblEmployees,FIND("support",LOWER(tblEmployees[Job Title]),1,0)>0))

 

4. Crete a new table called dSupportManagers

 

dRSCManagers = CALCULATETABLE(tblEmployees,FILTER(tblEmployees,tblEmployees[RSC Manager]))

I now have a table listing all managers who have a direct report with "Support" in their job title.

 

There's got to be a better way, right?

 

Doug

 

View solution in original post

5 REPLIES 5
Frequent Visitor

I just realized that filtering the table on Job Titles that contain the word "Support" won't help because the titles of the managers don't contain the word "Support" and those are the records I need to include, NOT filter out.

 

What might work is to use something like SUMMARIZE or VALUES to create a table listing the value of the REPORT TO column filtered on Job Titles that contained the word "Support". The Employee table could be filtered on FULL NAME using the results of the SUMMARIZED table.

 

Employees Table

 

Full Name],[Job Title],[Reports To]

John Doe, Support Analyst, Wiley Coyote

Sally Fields, Account Representative, Big Bird

Bob Builder, Sr. Support Engineer, Larry King

Steve Nash, Support Analyst, Wiley Coyote

Jamie Smith, Sr. Support Engineer, Larry King

Wiley Coyote, Manager, Brian Jeffers

Big Bird, Support Supervisor, Sarah Lee

Larry King, Director of Service, Brian Jeffers

Sarah Lee, Director of Service, Brian Jeffers

SUMMARIZE(tblEmployees,tblEmployees[Job Title] would return a list of people that are managers.

 

Wiley Coyote

Big Bird

Larry King

Sarah Lee

Brian Jeffers

 

If I could then FILTER the FULL NAME column of the Employees table against the SUMMARIZEd table I could easily pull out the manager records.

Is that the right approach and, if so, what would the expression/filters look like?

Doug

Frequent Visitor

I had another idea that also "busted". Use the original Employee table to create two new filtered tables.

 

tblSupportEmployees = CALCULATETABLE(tblEmployees,FILTER(tblEmployees, FIND("support",LOWER(tblEmployees[Job Title]),1,0)>0)

 

tblSupportManagers = CALCULATETABLE(tblEmployees,FILTER(tblEmployees,PATHLENGTH(PATH(tblEmployees[Full_Name],tblEmployees[Reports_To]))>1))

 

This would have given me a table of managers and a filtered table of employees that matched my criteria. I could have related the two tables using the FULL NAME column in the tlbSupportManagers table and the REPORTS TO column in the tblSupportEmployees table.

 

Unfortunately, there are managers listed in the REPORTS TO column that have no record in the tblEmployees table so PATH is complaining.

Frequent Visitor

This is my pathetic work around. I'm sure there is a much more elegant solution.

 

1. Create a lookup table called dSupportManagerNames

 

 

dSupportManagerNames = CALCULATETABLE(SUMMARIZE(FILTER(tblEmployees,FIND("support",LOWER(tblEmployees[Job Title]),1,0)>0),tblEmployees[Reports_To]))

 

2, Add a calculated column to tblEmployees indicating whether or not the employee is a Support Manager

 

RSC Manager = IF(ISBLANK(LOOKUPVALUE(dSupportManagerNames[Reports_To],dSupportManagerNames[Reports_To],tblEmployees[Full_Name])),FALSE,True)

3. Create a new table called dSupportEmployees

 

dSupportEmployees = CALCULATETABLE(tblEmployees,FILTER(tblEmployees,FIND("support",LOWER(tblEmployees[Job Title]),1,0)>0))

 

4. Crete a new table called dSupportManagers

 

dRSCManagers = CALCULATETABLE(tblEmployees,FILTER(tblEmployees,tblEmployees[RSC Manager]))

I now have a table listing all managers who have a direct report with "Support" in their job title.

 

There's got to be a better way, right?

 

Doug

 

View solution in original post

Hi,

 

Try this calculated column formula

 

=if(SEARCH("Support",Data[Job Title],,0)>0,Data[Reports To],BLANK())

 

Can you somehow make use of this now?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hey Ashish,

 

Thanks for the response. The calculated column would record the Manager's name in a new column for each employee with "Support" in their job title, which is great. But what I really need is the records for the names of those managers.

 

The Employee table is in a parent/child relationship. Each employee (child) has a manager (parent). I need to create a table of just the parents for children who have "support" in their job title.

 

Doug

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors