Hi I have a situation with security roles that I am not sure how to manage.
I have a table that has all employees names as well as their supervisor. Example below:
| Employee | Employee Email | Supervisor | Supervisor Email |
| Employee A | a@employee.com | - | - |
| Employee B | b@employee.com | Employee A | a@employee.com |
| Employee C | c@employee.com | Employee B | b@employee.com |
| Employee D | d@employee.com | Employee B | b@employee.com |
| Employee E | e@employee.com | Employee B | b@employee.com |
| Employee F | f@employee.com | Employee Z | z@employee.com |
This table is used by joining to a table of employee hours by employee ID (Not in the example table above).
What I want is when employee A views the report from PowerBI service they see only the employees they are supervisor of. The way I coded this right now in the security roles I have "Supervisor Email = userprinciplename()"
In the example above this would allow the Employee A to see the hours of Employee B in the report, but not the hours of employee C, D, and E, even though employee A is technically the parent supervisor of all of them. This is fine and works ok, since when employee A is chosen as the supervisor in the slicer I do want it to show just the hours for employee B. It will only show the hours for employees C, D, and E when employee B is selected. In this example the role security issue I run into is with a supervisor filter that I have at the top of the page.
I need to write the security roles in a way that when employee A logs in to view the report the security roles see that a@employee.com is logged in so they can view the data for b@employee.com, so when they go to the supervisor filter at the top they have the option to pick b@employee.com as the filter choice and see their employees hours, right now the only option they will get is themselves in this filter because of the way I wrote the role security.
When employee A logs in they only have themselves as a supervisor option, but they should also be able to filter the report to Employee B as supervisor too and see the employees under that child supervisor. In the real world supervisor A would be supervisor to multiple different child supervisors and need to be able to choose which set of employees they are looking at.
How would I write the security roles to be able to traverse down the chain of command and show all supervisors/employees under them in my slicer option?
Thanks for your help, let me know if I need to explain anything further.
