RLS Patterns That Scale Past One Team

Row-level security that works for one team's regional sales report often falls apart the moment a second business unit, a partner org, or a hierarchy of managers needs access to the same model. The fix isn't more roles — it's a security table designed for scale from the start.

Skip the hardcoded role, use a security table

Instead of a DAX filter that hardcodes USERPRINCIPALNAME() = "someone@co.com", model security as data:

[UserAccess] :=
'Security'[UserEmail] = USERPRINCIPALNAME ()

Where Security is a table mapping UserEmail to the dimension keys they can see — region, business unit, whatever the grain is. Adding a user, or an entire team, becomes a data change, not a model redeploy.

Handling hierarchies without exploding row counts

For manager-sees-direct-reports scenarios, don't try to enumerate every manager's full downline as rows in the security table — it grows combinatorially. Instead, store a path (like a materialized path or a ParentPath column) and filter with a path-prefix match:

[UserAccessByHierarchy] :=
VAR CurrentUserPath =
    LOOKUPVALUE ( 'Security'[Path], 'Security'[UserEmail], USERPRINCIPALNAME () )
RETURN
    PATHCONTAINS ( 'Employee'[Path], CurrentUserPath )

The one thing to actually test

Test RLS as the user, not as yourself with a role applied in Desktop. "View as role" with a hardcoded filter hides bugs that only show up under USERPRINCIPALNAME() — particularly casing mismatches between Entra ID and your security table. Normalize both sides to lowercase before comparing, or you'll debug a "phantom" access issue that's really just Someone@Co.com not equaling someone@co.com.