I have a simple model with a transactions table containing training records and a user table containing employee email, manager email and the PATH function. I set-up RLS on the users table as PATHCONTAINS('All Users'[PATH],USERPRINCIPALNAME())
The intent is that the signed in user to be limited to his/her own training records and those of all persons reporting to them via a management hierarchy. In addition, I created 6 slicers with relevance based on the signed-in user. All slicers work well and limit data access except for a supervisor slicer. If I filter here, I only see data for the direct reports of the signed-in user but not the selected supervisor’s direct reports and indirect reports.
Consider use case for a Senior Director having two Directors as direct reports and each direct report having two staff members if I proxy to a Senior Director(1) and chose Director(1), I only see the training records for Director(1) but not for the direct reports and indirect reports for Director(1).
I don’t need to set-up the slicer with the manager hierarchy but if a senior leader selects a supervisor, I want them to see all training records for this direct report and down the hierarchy. Basically, traverse down the hierarchy from that node of the management hierarchy and return all training records for anyone rolling up to the supervisor.
I have searched a lot but have not come across a simple or elegant solution. Is there a simple way to accomplish this?
Thanks in advance for your support and guidance.
