Forum Discussion

dougford99's avatar
dougford99
Frequent Visitor
8 years ago
Solved

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 ...
Need Help
  • dougford99's avatar
    dougford99
    8 years ago

    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

     

dougford99's avatar
dougford99
Frequent Visitor
8 years ago

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