Forum Discussion
RLS TeamLevel - Organization L4-6
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())
Step 1) Create one access table
Build a single table like this:
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:
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.
