Power BI Dynamic RLS: Test Access Before Sharing

For maintainable Power BI row-level security, separate the access rule from the report layout: identify the signed-in user, look up approved region keys, filter the region dimension, and test the resulting rows as a consumer. A hidden page, an app audience, and a slicer do not establish that data boundary.

This walkthrough uses a small Import model and fictional identities. It supports a user who can access several regions without creating a separate role for every department. The examples explain expected behavior; validate the DAX and service permissions in your own test workspace before distributing a real report.

Define the access contract first

Write the rule in language the data owner can approve: “A consumer can read sales rows only for the region keys explicitly assigned to their current organizational UPN. An unknown identity receives no sales rows.” Name the person who approves grants and removals. Report authors need a separate, documented administrative access policy.

Three questions must have different answers: Can this person open the report? Which model rows may they read? Can they edit the model? Microsoft documents that RLS restricts workspace Viewers; Admin, Member, and Contributor permissions allow editing and bypass that restriction. Publishing an app does not repair excessive underlying permissions. Check the actual recipient's permissions, rather than judging security from the author account. See Microsoft's RLS implementation guidance.

Build a four-row sales fixture

In a test file, use Enter data or import these fictional CSV tables. RegionKey and SaleID are whole numbers; Amount is a fixed decimal. Each sales row represents one sale.

SaleID,RegionKey,Amount
101,10,120
102,10,80
103,20,300
104,30,50

Name that table Sales. Create DimRegion:

RegionKey,Region
10,West
20,East
30,Central

Create UserRegion with the following text UPN and whole-number RegionKey values. The example.test addresses are fictional and cannot stand in for real tenant test identities.

UPN,RegionKey
[email protected],10
[email protected],20
[email protected],10
[email protected],20

Create one active, single-direction relationship from DimRegion[RegionKey], the unique one side, to Sales[RegionKey], the many side. Leave UserRegion disconnected in this deliberately small Import example. The role will explicitly check it. Do not add bidirectional relationships to make an unexplained filter path appear to work.

Before proceeding, reject blank UPNs, blank region keys, duplicate UPN-region pairs, and mappings to nonexistent regions. Also identify sales with unknown region keys. These are access-data defects, not values to silently assign to a default region. This fixture has four sales, four grants, and a full-data total of 550.

Create one dynamic role with an explicit match

In Manage roles, create RegionConsumers. Apply this filter to DimRegion in the DAX editor:

VAR CurrentUPN = USERPRINCIPALNAME()
VAR CurrentRegion = DimRegion[RegionKey]
RETURN
    COUNTROWS (
        FILTER (
            UserRegion,
            UserRegion[UPN] = CurrentUPN
                && UserRegion[RegionKey] = CurrentRegion
        )
    ) > 0

Each dimension row survives only if the mapping contains that user's grant for its key. With no matching grant, no region survives. Add a second filter in the same role on UserRegion so a consumer querying that table sees only their own mapping rows:

UserRegion[UPN] = USERPRINCIPALNAME()

The dimension relationship then carries the permitted region filter to Sales. This explicit lookup is easy to inspect for a small mapping. For a large model, evaluate a governed relationship-based design and its performance with the model owner. This example's DAX pattern is scoped to Import; do not assume every function or design has the same support in DirectQuery.

Use the tenant's actual UPN value, not a guessed email convention. An address printed in an HR export may differ from the sign-in identifier. Normalize the mapping under an agreed identity process, preserve its source, and retest renames. Do not turn a failed identity match into access to all regions.

Prove the row set, not just the total

Add these separate measures:

Sales Amount = SUM ( Sales[Amount] )

Visible Sales = COALESCE ( COUNTROWS ( Sales ), 0 )

Diagnostic Identity = USERPRINCIPALNAME()

Make a diagnostic page containing the identity card, the two measures, and a table with SaleID, Region, and Amount. Use View as with the role and a specific test identity. Verify the exact IDs below. A total alone can hide a wrong-region substitution with the same amount.

The Amount measure intentionally stays blank for no visible rows; the diagnostic count displays zero. Now select Central as Alex: the result must remain empty. Clear every slicer: Alex must still see only 101 and 102. Removing a visual filter must never expand access.

Microsoft's RLS design guidance explains that multiple role memberships combine allowed access. A restrictive role does not subtract access granted by another role. Inspect all memberships, including group memberships, when a test identity unexpectedly sees more data.

Publish and test the real consumer route

Publish to a controlled test workspace. Assign the approved consumer users or supported Entra security groups to the model role in the service. Reuse one managed group where appropriate, while keeping each user's region grants in the reviewed mapping. Confirm the group type is supported by the current service documentation.

Replace the fictional identities with dedicated organizational test accounts, refresh the mapping, then use Test as role and an actual consumer session to repeat the ID checks. Test the same app or report URL recipients will use. An author preview verifies a rule expression; a consumer session also exercises effective permissions and distribution.

Record the model version, mapping revision, refresh time, tester, effective workspace/model permissions, selected identity, expected IDs, and observed IDs. Include export or Analyze in Excel paths if consumers are allowed to use them. RLS is a model rule, so a chart that looks restricted is insufficient evidence for another permitted access path.

Diagnose an unexpected access result

For a revocation test, remove Casey's East grant in the test mapping. After a confirmed refresh, Casey should see 101 and 102, totaling 200. Restore only an approved grant. Test an unknown identity again after every security-rule change.

Keep access changes reviewable

Keep the report role stable while managing grants as governed data. Review inactive users, duplicate grants, region ownership, and effective author permissions on a defined cadence. A hidden mapping table improves author usability but is not an access control; the role filter is what restricts consumer queries to that table.

For modeling foundations, use the model-first Power BI project. For disagreements after security is verified, use the metric reconciliation workflow and compare the same authorized population.

FAQ

Does an app audience replace row-level security?

No. It controls distribution of app content. Define and test the model's row permissions separately, including the recipient's effective model permissions.

Why does the owner see all rows while a viewer is restricted?

The owner may have editing permissions that bypass RLS. Test with the intended consumer permissions and identity; do not use an administrator's ordinary view as the acceptance result.

Will changing an imported access spreadsheet revoke access immediately?

The imported model must receive the approved change. Confirm the successful refresh and then verify the actual consumer's row set. An edited source file alone does not prove that the deployed model has updated grants.