Row-Level Security for Multi-Site and Multi-Contract Reporting

Every FM service provider I work with hits the same wall. They build one clean set of dashboards for work order compliance, SLA performance and cost per asset, and then someone asks the obvious question: how do we let each client log in and see only their own buildings? The moment you have more than one audience for the same report, you need Row-Level Security. Get it wrong and you leak one client's numbers to another, which in a competitive FM market is the fastest way to lose a contract. Get it right and one dataset serves every client, every regional manager and every internal team without a single duplicate copy.

The requirement everyone hits

The pattern is always the same. You run maintenance for, say, twelve clients across forty buildings. Internally your regional managers each own a cluster of sites. Your supervisors own a handful each. And on top of all that, every client wants a login that shows their portfolio and nothing else. That is three different filtering rules layered on the same tables: filter by site, filter by contract, filter by cost centre, and sometimes all three for the same person.

Row-Level Security (RLS) in Power BI answers this by filtering the data model itself based on who is viewing. The filter travels with the dataset, so it applies in the service, in the mobile app, in embedded reports and in any export. That last point is the one people forget, and I will come back to it.

Static roles versus dynamic RLS

There are two ways to build this, and only one of them scales.

Static roles.

You create a role per region or per client and hard-code the filter into the role definition. "North Region" role filters Site[Region] = "North". "Client Acme" role filters Contract[Client] = "Acme". It works, and for two or three audiences it is honestly fine. The problem is maintenance. Every new client is a new role. Every new region is a new role. Every time someone moves teams you edit role membership by hand in the service. Past about eight roles it becomes unmanageable, and you will forget one, and the one you forget is the leak.

Dynamic RLS.

Instead of a role per audience, you build one role whose filter is driven by a bridge table that maps the logged-in user to the scopes they are allowed to see. Add a client? Insert rows in the bridge table. Move a supervisor? Update rows. No republishing, no touching role membership. The role definition never changes; the data does. This is the approach I use on every multi-client build.

The bridge table and the DAX pattern

The whole design rests on one permissions table. I keep it deliberately simple: one row per user per scope they are entitled to, with validity dates so access can be granted and revoked without deleting history.

A supervisor covering three buildings has three rows. A client contact seeing one building has one row. A regional manager covering fifteen sites has fifteen rows, or one row of scope_type = Region if you prefer to resolve regions upstream. The table is the single source of truth for "who sees what".

The role filter then reads that table for the current user and checks each row of the fact against it. Applied to the Site dimension, the DAX filter looks like this:

Site[SiteKey] IN
    SELECTCOLUMNS(
        FILTER(
            UserScope,
            UserScope[user_email] = USERPRINCIPALNAME()
                && UserScope[scope_type] = "Site"
                && UserScope[valid_from] <= TODAY()
                && ( ISBLANK( UserScope[valid_to] )
                     || UserScope[valid_to] >= TODAY() )
        ),
        "k", UserScope[scope_value]
    )

You apply the equivalent filter to Contract and CostCentre if those scope types are in play. USERPRINCIPALNAME() returns the viewer's login; in the service that is their Entra ID email. The validity check means an expired row silently stops granting access without anyone editing the report. One role, one filter expression per dimension, driven entirely by data.

The hard case that breaks most builds

Here is where naive implementations fall apart. Consider three real users on the same contract:

• A supervisor who covers three specific buildings.
• Their regional manager who covers the whole region those buildings sit in, plus twelve others.
• A client contact who sees exactly one building.

The tempting shortcut is to put a column on the Site dimension, something like Site[OwnerEmail], and filter on it. This collapses instantly. A site cannot have one owner when a supervisor, a manager and a client all legitimately see it. The relationship between users and sites is genuinely many-to-many: one user sees many sites, and one site is seen by many users. A single column on the dimension can only ever encode one of those directions.

The bridge table solves it precisely because it lives outside the dimension. The supervisor gets three rows, the manager gets fifteen, the client gets one, and the same building key appears in all three of their scope sets without conflict. There is no ownership to contest; there is only a list of grants. This is the entire reason the design is a bridge and not a dimension attribute.

The traps that leak data

A filter that works in your test model can still leak in production. These are the four that catch people, and I check every one before a multi-client dataset goes live.

1. Bidirectional relationships defeat the filter.

RLS filters flow in the direction your relationships propagate. If you have set a relationship to bidirectional to make a slicer behave, an aggregated visual can pull totals that ignore your row filter, because the cross-filter travels back up a path you did not intend. Keep relationships single-direction wherever RLS is in play, and if you must use bidirectional, set the relationship's security filtering behaviour explicitly and re-test the totals.

2. Composite models and DirectQuery.

In a composite model, RLS defined on the local model does not automatically govern a DirectQuery source that carries its own security, and chaining datasets can surface data the downstream role never intended to expose. If you connect Power BI to an operational source such as IBM Maximo in DirectQuery, decide deliberately where the filter is enforced, at the source or in the model, and confirm it is not silently skipped at the join.

3. Workspace roles bypass RLS entirely.

This is the one that surprises people most. RLS only applies to users with the Viewer role on the workspace or app. Anyone assigned Admin, Member or Contributor sees the whole dataset, unfiltered, no matter what your role says. So the internal analyst you added as a Member to "help maintain the report" is looking at every client's data. Grant clients and scoped internal users Viewer access only, and keep the elevated roles to the tiny group who genuinely need the full picture. This overlaps with the broader access model I describe in my note on RBAC design for CAFM systems.

4. Export to Excel leaks full detail.

RLS does travel with exports, which is good, but "Export data" from a visual can still hand a user the underlying rows behind what they can see, and if any of the traps above have widened their view, the export widens with it. Test exports as a scoped user, not just the on-screen visuals, and where the data is sensitive, restrict "Export data" and "Analyze in Excel" in the tenant and capacity settings. How much of this you can control depends on your licensing tier, which I cover in Power BI licensing and capacity planning.

Test protocol before go-live

I never sign off RLS by eyeballing it. I use Power BI Desktop's "View As" feature to impersonate specific users, then check the numbers against a matrix I have agreed with the client in advance. "View As" lets you enter a role and a specific USERPRINCIPALNAME value and see the report exactly as that person would.

The matrix is the deliverable that gets signed off. For each test user I write the expected result before testing, then record what actually rendered:

That last row is exactly the kind of failure "View As" surfaces: the site list looked right, but an aggregated card summed across the whole portfolio because of a stray bidirectional relationship. The visual passed a glance and failed the matrix. I test totals and cards as deliberately as I test tables, and I keep the signed matrix as evidence that access was verified, not assumed. Only when every row reads pass, including one deliberate "should see nothing" negative test, does the dataset go to the client.

A note on independence

I do not resell Power BI licences and I hold no vendor commission. The patterns here are what I have found reliable across real multi-client FM reporting builds. Microsoft changes service behaviour regularly, so treat the traps as a starting checklist and always verify against the current documentation for your tenant and capacity.

For the authoritative detail, the two references I keep open are Microsoft's own:

• Power BI Row-Level Security documentation
• DAX function reference

Conclusion

Multi-audience reporting is not a licensing feature you switch on, it is a design you commit to. Skip static roles the moment you have more than a handful of audiences. Drive everything from a user-to-permission bridge table, resolve the many-to-many reality instead of forcing a single owner onto each site, and treat the four traps as blockers rather than footnotes. Then prove it with "View As" and a signed matrix before anyone outside your team logs in. Do that and one dataset safely serves every client you have, which is exactly the leverage a growing FM provider needs.