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

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

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's avatar
dougford99
Frequent Visitor
8 years ago

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