skip to content

A Power BI RLS role filters DimRegion, yet the fact table still shows every row — why?

level: seniorimportance: should knowfreq 48%

answer

  1. RLS is only a filter that must travel
  2. follow the relationship, not the role
  3. one side to many side, by default
  4. a second fact may be joined to nothing
  5. check whether the tester can edit the model

basics

~20 s

The security filter only travels where model relationships carry it. Look for a missing, inactive or wrongly directed relationship, a fact table joined to a different dimension copy, or a disconnected table — and confirm the user is not exempt through workspace edit rights.

solid answer

~50 s

RLS is nothing more than a filter placed on a table, so it reaches other tables exactly as any filter does: along active relationships, in their cross-filter direction. When restricted rows still appear, work down the propagation path. Is there an **active** relationship from `DimRegion` to that fact, or is it inactive and only used by `USERELATIONSHIP` inside a measure, which does not restore security propagation? Does it point the right way — a filter on the many side does not reach the one side unless security filtering in both directions is enabled? Is the visual reading a *second* fact or a disconnected helper table that no relationship links to `DimRegion`? Then check the layer above the model: a user with workspace Admin, Member or Contributor rights bypasses roles entirely, so the model may be innocent.

code

text · 12 lines
text
-- BROKEN: the entitlement table is on the many side
UserPermission(UserEmail, RegionKey) *--->1 DimRegion   (single direction)
   role filter lands here ^                stops here ^

-- BROKEN: second fact never related to the filtered dimension
DimRegion 1--->* FactSales        restricted
FactBudget(RegionName, Amount)    no relationship -> unrestricted

-- WORKING
UserPermission *<-->1 DimRegion   (security filter in both directions)
DimRegion 1--->* FactSales
DimRegion 1--->* FactBudget

go deeper

for a junior

Understand that a role filters one table and other tables are only affected if a relationship connects them — so an unrelated table is not protected.

for a middle

Trace propagation deliberately: active versus inactive relationships, cross-filter direction, one side versus many side, and why bridges need security filtering in both directions.

for a senior

Show a diagnostic order — reproduce under simulation, walk the relationship graph, check every fact table, then rule out workspace edit rights and role membership before touching the model.

for a principal

Treat unrestricted propagation paths as an auditable risk: require every fact table to be covered by a test, and make new relationships and new facts trigger an RLS review rather than relying on the modeller's memory.

## The diagnostic frame Row-level security in Power BI is not a separate enforcement engine. A role puts a predicate on one table, and everything else that gets restricted is restricted **because a filter propagated there**. So "RLS is leaking" is almost always "the filter did not arrive", and the investigation is a model-relationship investigation. Work outward from the filtered table. ## 1. Is there a relationship at all? The classic case is a second fact table. `FactSales` was joined to `DimRegion` when the role was written; `FactBudget` was added later against its own region column and never related. The sales visual is correctly restricted and the budget visual is wide open. Put every fact table on a scratch page and check its total under a simulated identity — if a table is not on the propagation graph from the filtered table, it is unrestricted, and nothing in the report hints at that. A related shape is a duplicated dimension: two region tables in the model, the role written against one, the fact joined to the other. Both look plausible in the field list. ## 2. Is the relationship active? Only one relationship between two tables can be active. Filters — including the security filter — propagate along the active one. A measure that reaches an inactive relationship with `USERELATIONSHIP` changes which path *its own* calculation uses; it does not extend the security filter along that path in a way you should rely on. If the fact's only link to the filtered dimension is inactive, treat it as unrelated. ## 3. Does it point the right way? Single-direction relationships propagate from the **one** side to the **many** side. Filtering `DimRegion` therefore restricts `FactSales`. But a filter placed on a table on the many side — a user-permission bridge, an entitlement table, a many-to-many bridge — does **not** reach the one-side table by default. This is the signature dynamic-RLS bug: the filter lands on the entitlement table, stops, and every dimension and fact stays open. The fix is a bi-directional relationship or the relationship's security-filtering-in-both-directions option, which exists precisely because security propagation and report propagation are configured separately. ## 4. Is the model shape defeating propagation? Many-to-many relationships and composite models complicate the picture. A many-to-many relationship between two tables of the same grain, or a limited relationship across sources in a composite model, does not propagate identically to a regular strong relationship, and any assumption you make about a *report* filter arriving must be re-verified for the security filter. Snowflaked dimensions add another hop: a role on the outer level only reaches the fact if every intermediate relationship also carries the filter in that direction. ## 5. Is the number even coming from the model you think? Check what the visual actually reads. A field from a disconnected parameter table, a calculated table built at refresh time, or an imported summary table produced in Power Query is not filtered by a role on `DimRegion` unless something relates it. Calculated tables and calculated columns are materialised during refresh under the refreshing identity, so they can bake in unrestricted values that then display to everyone. ## 6. Is the user subject to RLS at all? Before blaming the model, confirm the test subject is read-only. Users with **Admin, Member or Contributor** roles in the workspace have edit permission on the semantic model, and RLS is not applied to them — they legitimately see everything. Testing with your own account, which is usually a workspace member, produces exactly the symptom in the question and wastes an afternoon. Test as a Viewer, or use the role simulation in Desktop or the Service. ## 7. Is the role assigned? A published role with no members restricts nobody. Conversely, roles are **additive**: a user in two roles sees the union of both. Someone added to a broad "All regions" role for a one-off request keeps that access forever and appears to bypass their narrow role. ## A workable order Simulate the identity in Desktop first — if the leak reproduces there, it is a model problem, and you can walk relationships in the model view until you find the break. If it does **not** reproduce in Desktop but does in the Service, it is a deployment problem: membership, workspace role, or an entitlement table whose refresh has not run.

  • How do you decide whether the fault is in the model or in the deployment?
    Reproduce it under role simulation in Power BI Desktop. If the leak appears there, the model's propagation is broken and you fix relationships. If Desktop restricts correctly but the Service does not, the model is fine and the problem is above it: workspace edit rights, missing or extra role membership, or an entitlement table that has not been refreshed since the permission change.
  • Does USERELATIONSHIP let a measure reach data the user's role excludes?
    No. `USERELATIONSHIP` activates an inactive relationship for the duration of one calculation, changing which path filters travel for that measure; it cannot remove the security filter, and the engine will not surrender rows the role excluded. Treat a fact whose only link to the filtered dimension is inactive as effectively unrelated for security purposes, and give it an active relationship instead.
  • Why is enabling bi-directional filtering to fix RLS a decision to make carefully?
    It changes report behaviour as well as security propagation — ambiguous paths, unexpected cross-filtering between dimensions, and slower queries. Prefer the narrower relationship option that applies the security filter in both directions, or restructure so the identity column sits on the one side. Turning on full bi-directional filtering across a model to fix one role is how ambiguity errors and mystery totals get introduced.

saying these in an interview costs you the question

  • Assuming a role protects every table in the model
  • Testing with an account that has workspace edit rights
  • Expecting a filter to travel from the many side automatically
  • Forgetting a newly added fact table was never related
  • Trusting a calculated table built at refresh to respect roles

context

resolving links…