Forum Discussion
Help required: Dynamic Row Level Security with conditions
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 | [email protected] | [email protected] | 1 | 0 | 0 | 0 | 1 | |
| Frank | 2 | [email protected] | [email protected] | 1 | 1 | 0 | 0 | 0 | 2 |
| Sophie | 3 | [email protected] | [email protected] | 1 | 1 | 1 | 0 | 0 | 3 |
| Ruby | 4 | [email protected] | [email protected] | 2 | 0 | 0 | 1 | 0 | 4 |
| Ben | 5 | [email protected] | [email protected] | 2 | 0 | 0 | 0 | 1 | 5 |
| John | 6 | [email protected] | [email protected] | 3 | 0 | 0 | 0 | 0 | 6 |
| Lisa | 7 | [email protected] | [email protected] | 3 | 0 | 0 | 0 | 0 | 7 |
| Paul | 8 | [email protected] | [email protected] | 1 | 1 | 1 | 0 | 0 | 8 |
| Rebecca | 9 | [email protected] | [email protected] | 1 | 1 | 0 | 0 | 0 | 9 |
| Lucy | 10 | [email protected] | [email protected] | 8 | 0 | 0 | 0 | 0 | 10 |
| John-Paul | 11 | [email protected] | [email protected] | 8 | 0 | 0 | 0 | 0 | 11 |
| Simon | 12 | [email protected] | [email protected] | 9 | 0 | 0 | 0 | 1 | 12 |
| Alan | 13 | [email protected] | [email protected] | 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
- Because of this Ben and Simon should be able to see the data of their team. For clarity;
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.
Please refer to this blog to set the RLS with organizational hierarchy:
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
2 Replies
- v-diye-msftCommunity Support
Please refer to this blog to set the RLS with organizational hierarchy:
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
- v-diye-msftCommunity Support
If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!