Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Filter data based on Manager - Employee relationship

I have an organizational chart that looks somewhat like this:

 

OrgChart.png

The data is in a table like below:

 

EMPLOYEE,MANAGER
Nicole,
Jim,Nicole
Dana,Nicole
William,Jim
Maria,Jim
Kate,Jim
Jane,Dana
John,Dana
Stewart,Kate
Scott,John
Osha,John

 

Then I have a second table that tracks some data for each employee. Suppose it's some task completion data.

 

EMPLOYEE,TASKS,COMPLETED
Nicole,12,8
Jim,10,5
Dana,8,8
William,13,11
Maria,15,13
Kate,4,4
Jane,16,12
John,3,3
Stewart,6,5
Scott,23,21
Osha,9,3

 

Based on this data I calculate a completion %.

 

Table1.png

 

So far, all good. But I'd like to be able to have a slicer and filter by manager, and when it does, I'd like to show data for all employees that directly report to that manager as well as those who report to managers under him/her. For instance, from above data if I select Dana, I'd like to show data for all four Jane, John, Scott, and Osha. But as it is if I select Dana it only shows data for employees directly report to her.

 

Table2.png

How do I set the relationships to display what I want. I don't have control over how the data source is organized, so all I do will have to be from within PowerBI.

 

5 REPLIES 5
Saniat
Helper V
Helper V

It's a nice question, and I am looking for a similar soln to my problem.

S_JB
Resolver III
Resolver III

A potential solution could be to add a new column using the LOOKUPVALUE function, see an example below:

Manager II = IF(LOOKUPVALUE('Hierarchy'[Manager],'Hierarchy'[Employee],'Hierarchy'[Manager])="Nicole",
'Hierarchy'[Manager],
LOOKUPVALUE('Hierarchy'[Manager],'Hierarchy'[Employee],'Hierarchy'[Manager]))


Also, the below article provides more information on the lookupvalue function if you want to make any changes to the logic:

 

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

Anonymous
Not applicable

This works, but only for the very specific example I asked. For instance it only basically shows for second level managers reporting to Nicole, but if I select Nicole it'll only show data for Jim and Dana. 

 

Organization structure can often change, and there can be managers added or removed chaning the 'level' etc. I wonder if there's a more general solution.

Need solution facing exact same issue.

Did you get a solution to this one ?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.