Row-Level Security

## The problem static roles have Static RLS hardcodes the permitted value in the role expression: a `West` role filtering `[Region] = "West"`, an `East` role, and so on. With four regions that is tolerable; with four hundred sales territories it is unmaintainable, and every joiner, leaver or territory change is a Power BI admin task. **Dynamic RLS** collapses all of that into one role whose filter is evaluated against the identity of whoever is running the query. ## The identity functions - `USERPRINCIPALNAME()` returns the signed-in user's User Principal Name — normally their email-shaped Entra sign-in name. It returns the UPN both in Power BI Desktop and in the Power BI Service, which is what makes it the recommended choice. - `USERNAME()` returns the UPN in the Service but `DOMAIN\username` in Desktop, so a model that works when published can behave differently while you develop it. Use it only when you deliberately want that value. - `CUSTOMDATA()` returns a value passed on the connection, which is how embedded scenarios can supply an identity that is not an Entra user. A critical rule: these functions belong in **role filter expressions and measures**, never in a calculated column. Calculated columns are materialised during refresh, under the refreshing identity, so a column defined as `[Email] = USERPRINCIPALNAME()` freezes one answer for everybody. ## Pattern A — the identity on the dimension If each dimension row has exactly one owner, put the owner's email on the dimension and filter it directly: ``` [ManagerEmail] = USERPRINCIPALNAME() ``` The filter reduces the dimension to that manager's rows and propagates one-to-many to every related fact. No bridge, no bi-directional relationship, minimal cost. It fails as soon as one row may be visible to several people. ## Pattern B — a user-permission table The general shape is an entitlement table, one row per (user, permitted key) pair: ``` UserPermission(UserEmail, RegionKey) ``` related many-to-one to `DimRegion`, with the role filter `[UserEmail] = USERPRINCIPALNAME()` on `UserPermission`. Here the filtered table is on the **many** side of the relationship, and filters do not travel from many to one by default. Either the relationship must be bi-directional, or you must enable security filtering in both directions on it, or the filter simply never reaches the dimension and the user sees everything. This is the single most common dynamic-RLS bug, and it is worth saying out loud in an interview because it shows you understand that RLS is nothing more than filter propagation. The entitlement table itself is ordinary data: it can come from an HR system, an access-request app, or a warehouse table maintained by the data team, and it updates on refresh. Two consequences follow. A permission change takes effect only after the next semantic-model refresh, and the entitlement table is now sensitive data inside the model — it should be hidden from the field list, and it is itself readable by nobody except through the role filter. ## Hierarchies "A manager sees their own team and everyone beneath them" is not a single join. The usual answer is to flatten the hierarchy upstream into the entitlement table — one row per (manager, employee) ancestor pair — so the DAX stays a plain equality test. Path functions such as `PATH` and `PATHCONTAINS` can express ancestry inside the model instead, but they cost more per query and are harder to reason about than a pre-computed bridge. ## Role membership With dynamic RLS there is normally exactly one role, and *everyone who consumes the report* is added to it — typically as a single Entra security group. A read-only user who is a member of **no** role cannot see the model's data at all, so forgetting to add a new consumer group looks like a broken report rather than a leak, which is the safe failure direction. ## Matching and hygiene DAX string comparison is not case-sensitive, so casing differences in stored emails will not break the match, but leading spaces, aliases, and stale addresses will. If the UPN people sign in with differs from the email your HR system stores, the join must be on the UPN — this mismatch is a frequent production incident. Guest (B2B) users sign in with their own UPN, which may not resemble your domain at all. ## Testing Desktop can view the report as a role and as another user, and the Service can test a role on the published model, so you can confirm the mapping without borrowing someone's account. Always test at least one user with several permitted keys, one with none, and one who is absent from the entitlement table entirely.