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 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.