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

[dougford99]

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

[dougford99 · Accepted solution]

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

[Ashish_Mathur]

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?

[dougford99]

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

[dougford99]

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.

[dougford99]

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