How do you test row level security before release?

Build a matrix with one line per test user, recording the row count and the aggregate total that user should see. Run every line and compare against the expectation. Include at least one user who should see nothing, because an unmapped user silently receiving everything is the failure that matters most and never looks wrong on screen.

Why is a row level security bug hard to spot?

Because the report still renders. A filter that is too tight returns a smaller number that looks like a quieter show, and a filter that is too loose returns a larger number that looks like a better one. Neither produces an error, a warning or a blank visual, so nothing prompts the reader to check.

Can you automate row level security testing?

Yes, by executing the same query once per test identity through an API that accepts an impersonated user name and returns the result. That turns a matrix of a dozen users into a script that runs on every release. The account running the script has to be a real user account, since service principals are not supported against models with row level security.

Testing row level security before one exhibitor can see another exhibitor data

BI and reportingUpdated 2026-08-237 min read

In short

Testing row level security means writing down every test user with the row count and total they should see, then running each one and comparing. A wrong filter returns fewer rows and looks plausible, so eyeballing a report catches almost nothing. The parts must also sum to the unfiltered whole.

The report looked right. The exhibitor performance page opened, the scan counts were plausible, the contracted value line moved in the direction the show had moved, and the account manager who was testing it said it looked fine. It was fine for her. She was mapped to four shows and the filter gave her four shows.

What nobody ran was the case where the filter is wrong in the other direction. Testing row level security means being specific about that in advance, because the failure mode here is silent by construction: a filter that returns the wrong rows produces a smaller or larger number and no error at all.

The failure that looks like a correct answer

Consider what each kind of bug produces on screen.

A filter that is too tight gives an account manager three shows where she should have four. Contracted value comes in lower than she expected, and the most natural explanation available to her is that the fourth show had a soft year. She might mention it. She probably will not, because a slightly disappointing number rarely triggers an investigation.

A filter that is too loose gives her five shows. Her patch looks 22 per cent bigger than it is, she is pleased, and nobody queries a good number. Meanwhile she has seen the contracted value of an account on a show she has no commercial relationship with, and if that account also buys from a competitor brand inside the same group, the exposure is real.

The one that ends careers is the unmapped user. Somebody joins, nobody adds their row to the mapping, and depending on how the filter is written they either see nothing or see everything. Seeing nothing produces a support ticket within an hour. Seeing everything produces silence.

None of these cases raises an exception. That is the argument for a written matrix instead of a look at the report.

What a test matrix has in it

One line per test identity, and for each line the answer you expect before you run it.

Take a portfolio of nine shows with a total contracted value of 4,182,000 across the current edition. Twelve test identities cover it: nine users scoped to one show each, one vertical lead scoped to three shows, one portfolio user with no filter, and one user with no mapping row at all.

The expected figures are arithmetic rather than opinion. The nine single-show users should see contracted values that sum to exactly 4,182,000, because the shows partition the portfolio. The vertical lead's total should equal the sum of her three shows, 517,000 plus 388,000 plus 604,000, which is 1,509,000. The portfolio user should see 4,182,000. The unmapped user should see zero rows.

Picking the twelve is a judgement worth making deliberately. Take the largest show and the smallest, because a filter bug that leaks 400 rows into a show of 60,000 hides better than the same bug in a show of 900. Take a biennial show if you run one, since its dimension rows behave differently in an off year. Take at least one person whose access spans a vertical, because multi-value mappings exercise a different code path from single-value ones. Fill the rest by coverage of the shows rather than by who happened to be available for a test.

Now run it and suppose the nine single-show figures sum to 4,206,000. The excess is 24,000, which means at least one contract row is visible to more than one show's user. That difference is the whole value of the matrix: it turns an invisible defect into a number you can chase to a row. Without the sum check, nine individually plausible totals pass nine individual inspections.

Why does testing as a role give the wrong answer?

Every tool has a preview feature and it is genuinely useful, right up to the point where the filter becomes dynamic.

The Power BI documentation (Microsoft Learn, 2026) is blunt about the limit: "The Test as role feature in the Power BI service uses your own identity when evaluating dynamic RLS expressions. This means USERPRINCIPALNAME() returns your UPN, not that of the user you're trying to simulate." The documentation adds that the feature simulates role membership without replicating another user's authentication context, and that validating a guest user's access means signing in as that guest.

So the preview answers a question about static roles and cannot answer the question you have once a mapping table is involved, which is the design most portfolios end up with, as covered in dynamic row level security mapping. Testing a dynamic filter through a preview that resolves to your own identity tests your own access twelve times.

There are smaller limits worth knowing before you build a process around the feature. It does not work for models using single sign-on against the source, it does not cover paginated reports, and it only shows reports that live in the same workspace as the model.

Running the matrix as an API call

The mechanism that actually solves this is an execute-queries endpoint that accepts an identity to impersonate.

Power BI's REST API documentation (Microsoft Learn, 2026) describes a request body with a queries list and an impersonatedUserName parameter, documented as "The UPN of a user to be impersonated. If the model is not RLS enabled, this will be ignored." You post a DAX query with the UPN of the test user, and the result comes back filtered as that user would see it.

That turns the matrix into a script. Twelve identities with two queries each is 24 calls, comfortably inside the documented ceiling of 120 query requests per minute per user, and it runs in seconds. The limits to design around are one query per call and one table per query, so each check is its own request rather than a single batched statement.

The output is a table of actual against expected that either matches or does not. Run it on every deployment of the model, keep the results, and a filter regression stops being something a director discovers.

The four checks that go against every test user

Row count comes first, before any aggregate. A count is harder to misread than a sum, because a sum can look reasonable while sitting on the wrong rows.

The aggregate total comes second, and it should be the measure people actually quote. Contracted value for a commercial model, verified attendance for an audience model.

The third is the partition check described above: the scoped users' totals summed against the unfiltered total. This is the only check that catches overlap, and overlap is the failure with commercial consequences.

The fourth is the negative test, and it is the one that gets dropped when the matrix is written in a hurry. An identity with no mapping row must return zero rows. An identity mapped to a show that has been retired must return zero rows. Write both down and run them, because the code path for "no match" is the path least likely to have been thought about.

When does the matrix get run?

On every change to the model, and specifically before the deployment that reaches the audience rather than after it.

Three moments justify an unscheduled run as well. When the mapping source changes, because a joiner and leaver feed that silently stops updating produces a correct filter over a stale table. When a new fact table joins the model, because a table that reaches the show dimension by a different path may not inherit the filter. And when somebody adds a relationship, since relationship direction decides whether the filter propagates at all.

The results are worth keeping in a file with a date rather than in a chat message. When an exhibitor asks how you know their data was never visible to a competitor, a dated matrix showing twelve identities and their expected totals is an answer. A recollection that somebody checked is not.

Where this stops

The matrix proves the filter behaves as specified. It says nothing about whether the specification is right, and it has one hard operational constraint.

The API route has to run as a real user account. The Power BI documentation states that service principals are not supported for datasets with row level security, so a fully unattended pipeline running under an application identity cannot execute this check. In practice that means a dedicated test account with the permissions to read the model, and a decision about how its credentials are held, which is a security conversation of its own.

The deeper limit is that row testing cannot see column exposure. Every one of the twelve identities can pass every check and still be reading a contracted value column that half of them should never see, because a row filter says nothing about columns. That is a different mechanism with a different test. And a matrix built on a filter design that was wrong from the start will pass cleanly and prove nothing, which is why the design conversation in row level security for event portfolios comes first.

Start with the partition check alone, this week, on the model you already have. Get the unfiltered total for one measure, then get the same measure for each scoped user, and add them up. If the two numbers differ, you have found a real defect in an hour, and you have the beginning of a matrix that the rest of the reporting work can be held to.

Questions people ask about testing row level security

How do you test row level security before release?: Build a matrix with one line per test user, recording the row count and the aggregate total that user should see. Run every line and compare against the expectation. Include at least one user who should see nothing, because an unmapped user silently receiving everything is the failure that matters most and never looks wrong on screen.
Why is a row level security bug hard to spot?: Because the report still renders. A filter that is too tight returns a smaller number that looks like a quieter show, and a filter that is too loose returns a larger number that looks like a better one. Neither produces an error, a warning or a blank visual, so nothing prompts the reader to check.
Can you automate row level security testing?: Yes, by executing the same query once per test identity through an API that accepts an impersonated user name and returns the result. That turns a matrix of a dozen users into a script that runs on every release. The account running the script has to be a real user account, since service principals are not supported against models with row level security.