Help required: Dynamic Row Level Security with conditions

[mark_carlisle]

I have the following example data and from this I would like to create dynamic row level security to simplify the process on managing permisson levels on reports and ensure people only see what they need to.

EmployeeName	EmployeeNumber	EmployeeEmailAddress	EmployeeUPN	ManagerEmployeeKey	IsManager	IsTrainingManager	IsCoach	IsDeputy	EmployeeKey
Mark	1	mark@example.com	mark@example.com		1	0	0	0	1
Frank	2	frank@example.com	frank@example.com	1	1	0	0	0	2
Sophie	3	sophie@example.com	sophie@example.com	1	1	1	0	0	3
Ruby	4	ruby@example.com	ruby@example.com	2	0	0	1	0	4
Ben	5	ben@example.com	ben@example.com	2	0	0	0	1	5
John	6	john@example.com	john@example.com	3	0	0	0	0	6
Lisa	7	lisa@example.com	lisa@example.com	3	0	0	0	0	7
Paul	8	paul@example.com	paul@example.com	1	1	1	0	0	8
Rebecca	9	rebecca@example.com	rebecca@example.com	1	1	0	0	0	9
Lucy	10	lucy@example.com	lucy@example.com	8	0	0	0	0	10
John-Paul	11	john-paul@example.com	john-paul@example.com	8	0	0	0	0	11
Simon	12	simon@example.com	simon@example.com	9	0	0	0	1	12
Alan	13	alan@example.com	alan@example.com	9	0	0	0	0	13

Visual the org stucture looks like this;

Example

• Mark, Frank, Sophie, Paul and Rebecca are all managers (IsManager = 1) so should be able to see all data in the report.
• Ruby is on a development opportunity and is coaching (IsCoach = 1) the new starters that the training managers manage (IsTrainingManager = 1)
  • Because of this Ruby should be able to see her own data plus those of John, Lisa, Lucy, John-Paul as they are managed by Sophie and Paul.
• Ben and Simon act as deputies (IsDeputy = 1) in their managers, Frank and Rebecca, absence.
  • Because of this Ben and Simon should be able to see the data of their team. For clarity;
    • Ben should be able to see his and Rubys figures
    • Simon should be able to see his and Alans figures

Essentialy its comes down to;

• IsManager = 1 should have unrestricted access.
• IsCoach = 1 should have access to only their data and that of the direct reports of the training managers, IsTrainingManager = 1.
• IsDeputy = 1 should have access to only their data and that of there managers direct reports.

I've tried a few things but I'm having issues with the logic.

[v-diye-msft]

Hi @mark_carlisle 

If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!

[v-diye-msft · Accepted solution]

Hi @mark_carlisle 

Please refer to this blog to set the RLS with organizational hierarchy:

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi