Insights / RLS in Power BI / Part 1
Part 1 of 20 · Build itPower BI RLS bridge table: get the model right
The problem. Your RLS test passed, and users still see the wrong data.
Why. The DAX is one line, [Email] = USERPRINCIPALNAME(), and it only filters the security table. Your relationships decide whether that filter ever reaches the fact table.
If you join the security table straight to a business dimension, both sides repeat regions, so you get a many-to-many relationship. That can cause ambiguous filter paths, slow queries, and results that look right until one user is mapped to two regions.
The fix. Put a small bridge dimension between them, with exactly one row per region:
UserRegionSecurity → DimRegion → DimCustomer → FactSales
(many) (one)
Every hop is now many-to-one or one-to-many. A user with two regions is just two rows in the security table.
The filter starts on the security table, so it has to travel "uphill" to DimRegion. On that one relationship only:
- Set Cross filter direction to Both.
- Tick Apply security filter in both directions.
Keep every other relationship single-direction.
-- role rule, on the security table
[Email] = USERPRINCIPALNAME()
Check before go-live
- The security table joins to a dimension with one row per key, not straight to the fact table Desktop
- "Apply security filter in both directions" is ticked on the security → bridge relationship only Desktop
- A user mapped to two regions sees both, and nothing else
Want a second pair of eyes on your own model? The RLS review checks your roles, relationships and workspace access against this checklist, at a fixed price.