Dynamic row level security mapping tables beat one static role per show

The security page on the portfolio model had forty one roles on it. Somebody scrolled through them on a call and read a few names out: Design_Shows, Design_Shows_Plus_Portfolio, Hospitality_Only, Hospitality_And_Kitchen, Finance_All_Revenue, AM_Patch_North, AM_Patch_North_Temp. The last one had been created for a two week handover in 2024 and still had a member in it.

Every one of those roles was created by a competent person solving a real request. Dynamic row level security mapping is the thing that would have stopped the pile from forming, and it works by moving the answer to "who sees what" out of the model definition and into a table of rows anybody can edit.

Forty one roles nobody maintains

Count how a portfolio gets to forty one without anyone doing anything foolish.

Nine shows, so nine single-show roles. Four verticals, so four more. One role for finance across all revenue. Twelve account manager patches, because exhibitor accounts do not respect show boundaries. Six combination roles created for people who span two shows and did not fit the existing set. Nine variants that show a single show plus the portfolio total, because directors kept asking to see where they sat. That is 41.

Each addition took twenty minutes and made sense on the day. The aggregate is a model that cannot be reasoned about. Nobody can answer "who can see the hospitality rate card" without reading forty one filter expressions and cross-referencing the membership of each, and because roles combine additively, two innocuous memberships can produce an access set that neither role author intended.

The static approach also puts every access change inside the model. Adding a role means editing in Desktop, republishing, re-adding members, and testing. If a portfolio of nine shows sees eighteen access changes in a year, which is modest for a sales team, and each one costs forty five minutes of a modeller's time end to end, that is thirteen and a half hours a year spent republishing a model so that one person can see one more show.

What the mapping table holds

The replacement is a table in the model with two columns that matter, and it is smaller than people expect.

One row per user per show they may see. Nothing else is required, though a start date, an end date and a source column earn their place quickly. For forty people across nine shows, the table came out at 63 rows: 26 people who see exactly one show, and 14 people who see between two and five, accounting for the other 37 rows.

Sixty three rows is a spreadsheet. It is also, importantly, a thing a sales operations manager can read and correct without opening a modelling tool. The role filter then does one job: given the identity of the person running the query, restrict the show dimension to the show keys that appear against that identity in the mapping table. Every fact table joined to the show dimension inherits the restriction, which is the mechanism covered in row level security for event portfolios and does not change here.

Microsoft's own documentation (Microsoft Learn, 2026) treats this as the normal case rather than an advanced one, stating that "Dynamic RLS is the most common approach because it allows a single role definition to filter data differently for each user, based on a user-mapping table in your data model." The implementation planning guidance (Microsoft Learn, 2026) names the three functions that make it possible, USERNAME, USERPRINCIPALNAME and CUSTOMDATA, and describes the result as a data-driven security design.

Which identifier does the filter actually compare?

This is where working implementations break, and the break is silent, because a mismatch returns no rows rather than an error.

The documented pattern is a single equality:

[UserEmail] = USERPRINCIPALNAME()

The trap is the assumption that the value on the left is the person's email address. The Power BI documentation is careful about this and worth quoting: the value returned "is the user's sign-in identifier (UPN), not necessarily their email address. For most users these are the same, but they can differ (for example, when a user's email is an alias)." The guidance that follows is to build the mapping table from the identity the function returns rather than from the mail attribute in the directory.

External accounts add a second format. A guest user from a partner organisation may resolve as their own address, or as a tenant-rewritten form containing an #EXT# marker followed by your tenant domain. The documentation says the format is not guaranteed and must be validated in your environment. For an event business this is not an edge case, because the people you most want to give scoped access to, venue partners, association co-organisers, an agency running one show's audience acquisition, are all guests.

The practical consequence is a rule I would treat as mandatory: before a mapping table goes live, have one real external user open a report containing a card that displays the identity function's output, and read what it says. Ten minutes of somebody's time against a class of bug that presents as an empty report and gets diagnosed as a refresh failure.

The role count after the rewrite

Forty one roles collapse to three, and the three are worth naming because the split is not arbitrary.

A portfolio role with no filter at all, holding the handful of people who genuinely see everything: group commercial, group finance, the two data modellers. Membership here should be short enough to read in one glance.

A show-scoped dynamic role, holding everybody whose access is described by the mapping table. This is the role that used to be thirty something roles.

An account-scoped dynamic role for exhibitor account managers, whose patch cuts across shows and therefore cannot be expressed as a set of show keys. It uses a second mapping table, user to account, and filters the exhibitor dimension instead of the show dimension.

Three roles is a security page a person can audit in a minute. The forty one row expressions that used to encode policy are now 63 rows of data plus one expression per role, and the policy is legible to the people who set it rather than only to the person who wrote the DAX.

What changes when access changes

The operational difference is the whole argument.

Under the static design, giving the hospitality director access to the kitchen show meant a model edit, a republish, a membership change and a test pass, in that order, by somebody with modelling access. Under the mapping table it is one insert, applied on the next refresh of the mapping source.

Eighteen changes a year go from roughly thirteen and a half hours of modeller time to eighteen rows, and the work moves to the person who actually knows whether the access is correct. That second effect matters more than the time saved. A modeller executing an access request cannot evaluate it. A sales operations manager maintaining the mapping table can look at a request and say that the person left the vertical in March.

Access removals get faster in the same way, which is the direction that carries the risk. A leaver who needs to be cut from nine shows is nine deleted rows on the day, with no queue behind a release.

Where does the mapping table come from?

Typing it is fine to start. Keeping it typed is how it drifts.

The durable version derives the table from a system that already knows the answer. A show assignment table in the CRM, an HR system's cost centre, a group membership in the identity directory read through a scheduled job. Whichever source you pick, the mapping table becomes the output of a refresh rather than a hand-maintained artefact, and it inherits whatever joiners and leavers process that source already has.

Keep the source column on the table even so. When a director says they cannot see a show, the first question is which row is missing, and the second is which system should have produced it. A table where every row records where it came from answers both in one query.

Where this stops

A mapping table moves the policy out of the model. It does not make the policy correct, and it introduces one failure mode the static design did not have.

If the mapping source breaks, everybody's access changes at once and quietly. A failed refresh that leaves the table empty gives every user in the dynamic role zero rows, which looks like a broken report. A refresh that loads a stale copy is worse, because a leaver's rows come back and nothing looks wrong at all. Both argue for a row count check on the mapping table as part of the refresh, with a failure that stops the refresh instead of publishing.

The second limit is that the pattern is only as good as its proof. A filter expression that compiles and returns plausible rows can still be returning the wrong ones, and no amount of table design catches that. Proving it needs a matrix of users and expected totals, which is the testing job rather than this one, and it needs a different approach again once the report is being served to external tenants through an embedded surface.

The first step takes an afternoon. Export your current role list from the model, put it in a spreadsheet, and against each role write the number of members and the set of shows it grants. Sort by member count. Every role with one member is a mapping table row that got promoted into a role definition, and on most portfolio models built this way that will be more than half the list.