Forum Discussion
RLS TeamLevel - Organization L4-6
Background:
All started with stakeholders wanted us to add RLS on Level 5 for some people.
We created an Azure Security Group and added the persons to that group and created table "TeamAccesSecurityL5" as in the pictures. We solved it like the model looks in the picture.
All worked fine and the users can see all salespersons related to that level.
New Requirements:
Now they came back and also wanted us to add Level 4 and Level 6.
As you can see this solution do not scale and work.
We need to rethink and do a more dynamic solution.
It's offcourse possible to add the same TeamAccessSecurityL5 table 2 times with L4 and L6.
But I don't like that solution. This needs to be possible to solve more dynamic.
Do you have suggestions on how we can solve this?
Step 1) Create one access table
Build a single table like this:
This table defines who can access which organizational unit at which level.Step 2) Create a normalized bridge table from your hierarchy
Today your model probably has columns like:
- L4 Org Unit ID
- L5 Org Unit ID
- L6 Org Unit ID
Create a new bridge table by unpivoting those columns into this format:
So each salesperson/entity will have multiple rows, one row per hierarchy level.Step 3) Keep your base dimension/fact model as is
Do not try to directly relate one security table to 3 different columns in the same table.
Instead, keep your existing fact/dimension tables unchanged, and use the new bridge table as the connection point.
- TeamAccess → security table
- OrgBridge → normalized hierarchy table
- Salesperson or Sales Unit → main business table
- Fact → sales/fact table
Step 4) Create relationships
Then let the filter flow from:
- TeamAccess → OrgBridge → Salesperson/Org table → Fact
Power BI cannot relate on two columns directly, so create a combined key such as:
- LevelOrgKey = [Level] & "|" & FORMAT([OrgUnitID], "")
Step 5) Add the RLS rule only on the access table
Apply RLS on TeamAccess only:
- LOWER(TeamAccess[Email]) = LOWER(USERPRINCIPALNAME())
4 Replies
Step 1) Create one access table
Build a single table like this:
This table defines who can access which organizational unit at which level.Step 2) Create a normalized bridge table from your hierarchy
Today your model probably has columns like:
- L4 Org Unit ID
- L5 Org Unit ID
- L6 Org Unit ID
Create a new bridge table by unpivoting those columns into this format:
So each salesperson/entity will have multiple rows, one row per hierarchy level.Step 3) Keep your base dimension/fact model as is
Do not try to directly relate one security table to 3 different columns in the same table.
Instead, keep your existing fact/dimension tables unchanged, and use the new bridge table as the connection point.
- TeamAccess → security table
- OrgBridge → normalized hierarchy table
- Salesperson or Sales Unit → main business table
- Fact → sales/fact table
Step 4) Create relationships
Then let the filter flow from:
- TeamAccess → OrgBridge → Salesperson/Org table → Fact
Power BI cannot relate on two columns directly, so create a combined key such as:
- LevelOrgKey = [Level] & "|" & FORMAT([OrgUnitID], "")
Step 5) Add the RLS rule only on the access table
Apply RLS on TeamAccess only:
- LOWER(TeamAccess[Email]) = LOWER(USERPRINCIPALNAME())
Hi tobny76
Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.Hi tobny76
We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.
Hii tobny76
To make the RLS solution scalable for Organization L4–L6, avoid creating separate tables for each level. Instead, build one dynamic security table that contains UserEmail, EmployeeID (or SalespersonID), and OrganizationLevel (L4/L5/L6). This table should map each user to the employees or teams they are allowed to see across all levels. Then create a relationship between this security table and the employee/sales table, and apply a single RLS rule like:
[UserEmail] = USERPRINCIPALNAME()Because the hierarchy (L4–L6) is already stored in the table, Power BI will automatically filter all related salespeople dynamically. This approach removes duplication, supports any future levels, and keeps the model easier to maintain and scale.
