Power BI Row-Level Security at scale — one dataset, two audiences.

An embedded Power BI architecture scoped per authenticated client via Row-Level Security, delivered for a national postal and logistics operator. One dataset serves ~10,000+ external clients and internal users, without duplication, and without ever exposing the wrong client's data.

Before this report existed, clients had two options: look up individual packages one at a time on the public tracker (no login, just a tracking number), or ask their commercial contact at the company to manually prepare a delivery-quality summary. Multiply that across roughly ten thousand client companies on weekly, monthly, and on-request cadences, and that's the volume of manual reporting the commercial team was absorbing.

The brief

The commercial team wanted to give each client self-service access to their delivery quality of service: the same aggregated SLA view internal teams were already using, filtered to that client's contracts and translated into language a logistics customer could read without a glossary. The deliverable had to live inside the client site so that logging into the site was the only credential a client needed. No new accounts, no separate dashboard URLs, no client-side configuration.

Existing internal reports for the same data already covered operational, executive, commercial, financial, and audit views. None of them were appropriate to expose directly. The internal nomenclature wasn't readable to clients: events like "on expedition" meant something specific inside the company that had no equivalent in the public language clients used (we'd say "object in transit" instead). And the internal models carried columns that clients should never see.

The constraint that shaped everything

The natural Power BI-native answer to per-client access is one workspace per client, with the client's users invited to their workspace. That was ruled out long before this project began. Power BI Service requires an internal-tenant account for every user, and granting 10,000+ external companies internal accounts wasn't viable from a security or licensing perspective. The security team had given that answer to a previous client-facing report effort, so by the time this project came up, embedded + row-level security was the established institutional pattern for any external delivery. The design conversation started from "embedded is the path"; the question was how to make the access mechanics correct at scale.

The architecture

What I delivered was a Power BI dataset built on a SQL view over the existing internal facts, plus a new bridge dimension that mapped each authenticated site user to the set of (client, contract) scopes they were allowed to see. The site team embedded the report using the Power BI App Owns Data pattern, passing the authenticated user's email as effective user identity. A row-level security rule on the bridge dim used USERPRINCIPALNAME() to filter the bridge to that user's rows, and the bridge's relationships propagated the filter downward to the fact view.

Two RLS roles, one model. The "client" role keys off USERPRINCIPALNAME() against the email column in the bridge dim. The "see-all" role applies no filter at all, but only internal users with explicit Power BI workspace permission can open the report in the first place. Internal access is governed by tenant SSO plus workspace permissions; external access is governed by the embed token. The RLS layer sits on top of both: it doesn't replace the upstream access controls, it scopes what each authorised viewer can actually read. From a maintenance standpoint, there is one dataset, one set of measures, one lineage to reason about.

The bridge dimension — where access control lives

The bridge dim is the smallest, most boring object in the whole architecture, and also the one that does all the real work. It maps each authenticated user to the set of (client, contract) scopes they're allowed to read: one row per (user, client, contract) triple. A user with access to five contracts appears as five rows; a contract shared across several site users appears for each of them. That many-to-many shape is non-negotiable; corporate clients routinely have multiple users at multiple contracts, and a stricter grain would have forced the model to choose between correctness and clean joins.

Once the bridge dim has the right grain, the RLS rule is a one-liner. The DAX shape, in pseudocode:

-- "client" role: applied on Bridge_UserScope
[email] = USERPRINCIPALNAME()

-- "see-all" role: applied on Bridge_UserScope
-- (none — table filter expression returns TRUE)

The bridge dim is materialised in SQL Server and refreshed daily. Source rows come from the site's user table (the authoritative source for which emails can authenticate), merged with the existing client master and contract data already maintained by other upstream systems. Everything downstream of the site feed (corporate restructures, contract reassignments, new users onboarded) is handled in the daily refresh. The freshness story matters because identity changes are rare enough that 24-hour latency is acceptable; the SLA data itself is much fresher.

The fact as a view, not a copy

The "new" client-facing fact was implemented as a SQL view on top of the existing internal QoS fact tables, not a separately materialised table with its own pipeline. The view does three jobs at once:

• Surface area reduction. Only client-safe columns exist in the view. If someone later adds an internal-only column to the underlying fact, it doesn't surface in the client report unless the view is deliberately changed.
• Semantic translation. Internal event names ("on expedition", and other internal terms) are projected to the public language a logistics customer recognises ("object in transit"). The translation lives in one place (the view), not scattered across DAX measures or report visuals.
• Maintenance independence. Internal reports can keep evolving without risk of accidentally breaking the external one. The view is the explicit contract between the two worlds, and every schema change has to pass through it.

Trade-offs and decisions

Asymmetric refresh, on purpose

The fact view inherits the underlying internal facts' refresh cadence: multiple times per day, driven by the existing QoS model. The bridge dim refreshes once a day. That asymmetry is deliberate: client-visible metrics need to be fresh because that's the product, while identity and contract assignments change slowly enough that overnight latency is fine. Pushing identity to near-real-time would have added cost with no client-side benefit.

The 24-hour identity lag — and why no one fought it

A new site user added at 10 a.m. doesn't see the report until the following day. In five years of production, no one has complained about this. Contract reassignments and new user onboarding aren't time-critical events on the client side. The operationally critical thing is that when a user sees the report, they see the right data.

Performance at 10k+ clients

No measurable performance impact from the RLS rule even at peak. The bridge dim stays small relative to the fact (one row per user-contract assignment, not per delivery), and the filter propagates cleanly through the star. If we'd modelled the security at the fact-row level, this would have been a different conversation.

Edge cases and leaks

None observed over 5+ years. That is the kind of statement that sounds like bragging until you've done it, and then you realise it's mostly the result of keeping the model boring. The hard cases (executives needing to see everything; a client with re-org changes; a contract reassigned mid-month) all collapsed into one simple operation: change the rows in the bridge dim. No DAX changes, no report changes, no role changes.

Outcome

The report went into production in early 2021 and is still running. ~10,000+ client companies have access today. The manual commercial-team workflow it replaced (preparing client-by-client SLA summaries on weekly, monthly, and on-request cadences) went away. The pattern has since been the template for every conversation about external Power BI delivery inside the company.

If I were rebuilding it today

I'd keep Power BI Embedded for the rendering layer: the star model is clean, the dataset isn't complex, and the architectural cost of migrating to a newer stack (dbt with warehouse-level row access policies, or Unity Catalog row filters) would outweigh the benefit. The model isn't fighting me, and the tooling around it has matured rather than been replaced.

Where I would push further is near-real-time client-facing alerts. The current architecture is pull-based: clients log into the site and read. A natural extension would be proactive notification when a client's batch is trending toward an SLA breach. That's the same monitoring-and-alerting pattern I later built on the internal operations side, pointed at a different audience. For 10k+ clients the implementation layer would need to be more robust than what's sufficient for internal operations, but the architectural shape (event detection, threshold logic, identity-aware delivery) carries across directly.

What I'd take into the next role

Most of the work in a project like this isn't the headline technology. It's noticing the boring decisions (where the contract lives, which side owns the refresh, what grain the access table needs to be at) and getting those right so that the visible technology can stay simple. Five years on, the same pattern still answers every question I had at the start, and that's the strongest signal I have that it was the right shape.