Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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?
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |