Forum Discussion
Create a slicer function to drill down by manager (org chart)
Hello,
I have created an org chart (sample data shown below)
| Employee ID | Employee | Manager ID | Manager Name | path | path length |
| 1 | Jane D | 3 | John D | = PATH('Org Chart'[Employee ID], 'Org Chart'[Manager ID]) | = PATHLENGTH('Org Chart'[path]) |
| 2 | Adam S | 3 | John D | ||
| 3 | John D | 4 | Susan P | ||
| 4 | Susan P | ||||
| 5 | Greg L | 7 | Dan G | ||
| 6 | Patricia M | 7 | Dan G | ||
| 7 | Dan G | 4 | Susan P |
from this, I am looking to be able to slice my data so that what I see on screen only pertains to individuals beneath a certain level.
The data involves support tickets closed, and so as an example:
Susan is the top of the chart, with two groups managed by John and Dan. For instance, I would like to see all tickets closed by people within Dan G's group (Greg & Patricia) and exclude those who are outside of that group (ie. everyone under Susan that isn't part of Dan's group -- John's group).
Has anyone come up with a way to do this? My initial thought was a slicer, but I am not sure if you're able to drill down in slicer. First selection would be Susan, then within Susan's group I'd drill down to Dan.
The table is tied to the ticket database via the employee name, so I have no problem slicing by an individual person, I would just like to be able to bucket the slicer based on management.
Thank you!
Any suggestions/advice is greatly appreciated.
i got you .
honestly, just now i came up with this approach .
this is the end result .
so to achieve this, what i did is the following :
step 1 : add pathlength to the table. this will show me the level of each employe in the hierarchy .step 2 : since i have 3 levels, so i have created 3 tables with the following code for each table.
so basically it is the same code, just changing the filter.
this way i get in each table the employees per level.
step 4 : now you need to build the relationnship between the tabes :
you relate employe from level1 to manager in level2 ,
and employe from level 2 to manager in level 3
last step :
you get the employe names from the tables into the visual.
things to note :
1.im not sure how scalable this solution would be for a complex model .
2. if in the future you have 4 levels, 5 levels, or more, you need to create w table per each level .
3. as you can see, the idea here is that you need a recrusive relationship which is not supported in power bi ( as far as i know )
4. you need to work on the way these tables should filter the rest of the model . since the filter is reading from them, i think ( not sure though ), if you link employye id from these tables to the fact tables and other tables in the model, you should be fine .
hope this will help you, or maybe give a hint into another solution maybe .
best regards
Daniel
5 Replies
hello ChrisR22
is this what you want ?
if you filter on suzan, it will show all employees under suzan,
howerver if you filter Dan G, it will only show employees under Dan G
Daniel29195 that is! Ideally the slicer doesn't show all employees, but rather starts at the highest level and then each field drills down. There are hundreds of employees in our department, only a dozen or so managers, and a couple Managing Directors, so it would be nice to not have to sift through all the names just to get to the managers or MDs.
so what you want is a hierarcht slicer,
at first you have the let us say, ceo ,
if you drilldown the slicer,, you will have people beneath it , etc...
if that is the case, my question would be, how many levels is there ?
Daniel29195 thats exactly what we are looking for. as of now there are 4, but we might expand this up so i'd like to know a method that will allow me to expand this to more levels when we incorporate higher level individuals in this data
i got you .
honestly, just now i came up with this approach .
this is the end result .
so to achieve this, what i did is the following :
step 1 : add pathlength to the table. this will show me the level of each employe in the hierarchy .step 2 : since i have 3 levels, so i have created 3 tables with the following code for each table.
so basically it is the same code, just changing the filter.
this way i get in each table the employees per level.
step 4 : now you need to build the relationnship between the tabes :
you relate employe from level1 to manager in level2 ,
and employe from level 2 to manager in level 3
last step :
you get the employe names from the tables into the visual.
things to note :
1.im not sure how scalable this solution would be for a complex model .
2. if in the future you have 4 levels, 5 levels, or more, you need to create w table per each level .
3. as you can see, the idea here is that you need a recrusive relationship which is not supported in power bi ( as far as i know )
4. you need to work on the way these tables should filter the rest of the model . since the filter is reading from them, i think ( not sure though ), if you link employye id from these tables to the fact tables and other tables in the model, you should be fine .
hope this will help you, or maybe give a hint into another solution maybe .
best regards
Daniel
