Back to Blog

Power BI Row-Level Security - What Actually Goes Wrong and How to Get It Right

March 11, 2026•9 min read•Michael Ridland

We've been called in to fix broken row-level security (RLS) implementations more times than I'd like to admit. The scenario is usually the same: an organisation built a Power BI report, added some RLS rules that seemed to work during testing, then six months later someone notices that a regional manager can see payroll data for the entire company. Or the opposite - an executive can't see anything at all because the RLS filters are too restrictive.

RLS in Power BI is one of those features that's simple in concept but surprisingly easy to get wrong. Microsoft recently updated their official RLS guidance, and the technical detail is solid. But the gap between reading the documentation and actually building RLS that works reliably in production is where most teams get stuck. Here's what we've learned from building these systems for Australian organisations across finance, healthcare, and government.

The Biggest Misconception About RLS

Most people coming from a SQL Server background assume that Power BI's security model works like database permissions. It doesn't. In SQL Server, if you deny access to something, that denial wins - even if another permission grants access. The "once denied, always denied" principle.

Power BI RLS doesn't work that way. When a user belongs to multiple roles, the filters are additive. They see the union of what all their roles allow. This trips up almost every team we work with.

Here's a concrete example. Say you create two roles:

  • Workers - a rule that returns FALSE() on the Payroll table, blocking all rows
  • Managers - a rule that returns TRUE() on the Payroll table, showing everything

You might think that if someone is in both roles, the denial wins. It doesn't. They see all payroll data because the TRUE() from the Managers role gets unioned with the FALSE() from the Workers role.

This has real consequences. We worked with a financial services firm that had built separate roles for "can see revenue data" and "cannot see revenue data." The problem was that their security group structure meant some users ended up in both roles - and the permissive role always won. They didn't catch it for months because the people in both roles were senior enough that nobody questioned why they could see everything.

The fix: Design your roles so that a single role grants all the permissions a user needs. Don't try to layer deny-style roles on top of allow-style roles. It won't work the way you expect.

Getting Your Role Design Right

When we design RLS for clients, we follow a few principles that have saved us from painful debugging sessions.

One role per user profile, not per table. Instead of creating roles like "Can see Sales" and "Can see Marketing," create roles like "Regional Manager - NSW" or "Department Head - Finance." Each role should contain all the filter rules that user type needs. This makes it much easier to reason about what a given person can see.

Use security groups, not individual users. I know this sounds basic, but you'd be surprised how many organisations we audit where RLS role membership is managed by manually adding individual email addresses. When someone changes departments or leaves, their access lingers. Map your RLS roles to Microsoft Entra ID security groups and let your IT team manage group membership through their normal processes. It's less exciting than managing it yourself, but it's the only way that scales.

Keep the number of roles small. Every role you create is a role you have to test and maintain. We've seen models with 40+ roles, each with slightly different filter combinations. Testing all of those is practically impossible. If you can't describe your security model in a paragraph, it's probably too complicated.

The Validation Problem Nobody Talks About

Here's something that causes real security issues: how you handle unexpected values in dynamic RLS rules.

A lot of teams use the USERNAME() function to apply filters based on who's logged in. That's fine. But the way they write the filter expression often leaves a gap.

Consider this common pattern:

IF(
    USERNAME() = "Worker",
    [Type] = "Internal",
    TRUE()
)

The intent is that Workers only see internal records, and everyone else sees everything. But "everyone else" literally means everyone else - including typos, test accounts, and any value that isn't exactly "Worker." If someone passes "Wrker" as the username (which can happen in embedded scenarios), they get full access.

The safer version explicitly checks for every expected value:

IF(
    USERNAME() = "Worker",
    [Type] = "Internal",
    IF(
        USERNAME() = "Manager",
        TRUE(),
        FALSE()
    )
)

Now, any unexpected value returns no rows. This is a much better default - fail closed rather than fail open.

We apply this principle across every RLS implementation we build. Every branch of a conditional filter expression should have an explicit outcome. The fallback should always be FALSE(), never TRUE(). If you're using Power BI embedded where your application code passes identity information, this is not optional. Application bugs that pass malformed identity data should result in users seeing nothing, not everything.

RLS Performance - Where It Bites You

RLS filters run on every single DAX query that hits your model. Every visual on every page, every time a user interacts with a slicer. If your RLS is inefficient, your entire report is slow for every user, all the time.

We covered performance optimisation broadly in our Power BI optimisation guide, but RLS adds some specific considerations.

Filter on dimension tables, not fact tables. Your fact table might have millions of rows. Applying an RLS filter directly on a fact table means Power BI has to evaluate that filter against every row. Instead, apply the filter on a dimension table and let the model relationships propagate it to the fact table. A filter on a Region dimension table with 50 rows is far cheaper than a filter on a Sales fact table with 10 million rows.

This is just good star schema design. If your model follows a proper star schema - and it should - RLS on dimensions will naturally propagate through your relationships to filter the facts.

Don't use LOOKUPVALUE when a relationship will do. We see this a lot. Someone writes an RLS rule that uses LOOKUPVALUE() to cross-reference a user's email against a security table. If there's a relationship between those tables, just use the relationship. It's faster and easier to maintain.

Use Performance Analyzer to measure the impact. Power BI Desktop has a built-in Performance Analyzer that shows you exactly how long each query takes. Run your report without RLS, note the query durations, then use View As to apply RLS and compare. If you see queries doubling in duration, your RLS filters need optimising. Don't guess - measure.

If you're on DirectQuery, your source database needs to be ready. RLS filters get translated into SQL predicates that hit your source database. Make sure you have appropriate indexes on the columns your RLS filters reference. We worked with one government department where RLS on a DirectQuery model added 15 seconds to every page load because the underlying database didn't have an index on the employee region column. A single index creation brought it down to under a second.

Partial RLS - When Users Need to See the Bigger Picture

There's a common reporting requirement that seems to contradict RLS: showing a user their own data alongside a total for comparison. "Your region did $2M in revenue, which is 15% of the company's $13.4M total."

You can't just override RLS in a DAX expression. It doesn't work that way - and that's by design, since it would be a security hole.

The workaround is a summary table. You create a calculated table or aggregation table that stores pre-computed totals without any RLS applied to it. Because this table has no RLS rules, it always returns the full total. Your measures then reference this summary table for the "all regions" figure while the main tables remain filtered by RLS.

It's a clean pattern once you understand it, but it does mean you're maintaining a separate summary structure. Keep it simple - aggregate only the specific metrics you need for comparison, not entire copies of your data.

Testing Properly

I'll be blunt: most organisations don't test their RLS properly. They check that it works for one or two users and call it done.

Here's what thorough testing looks like:

  1. Test every role using View As in Power BI Desktop. Verify that each role sees exactly what it should - no more, no less.
  2. Test with expected values for dynamic rules. If your RLS uses USERNAME(), test with actual usernames that will be used in production.
  3. Test with unexpected values. Pass garbage strings, empty strings, null values. Make sure the filters return no rows for anything you didn't explicitly plan for.
  4. Test multi-role membership. If a user could end up in two roles, test that combination. Verify the union of filters produces acceptable results.
  5. Document what each role should see. Before you test, write down the expected outcome. "Users in role X should see rows where Region = NSW and Department = Sales." Then verify against that specification.

We build a test matrix for every RLS implementation. It adds maybe a day to the project, and it's saved us from embarrassing security gaps more than once.

When to Bring in Help

If your Power BI model serves sensitive data - financial results, HR information, customer PII - getting RLS wrong isn't just inconvenient, it's a compliance issue. We've helped organisations across Australia build and audit their RLS implementations, particularly in regulated industries where data access controls need to withstand audit scrutiny.

If you're not sure whether your current RLS setup is secure, or if you're planning a new implementation and want to get it right from the start, our Power BI consulting team can review your model design and security configuration. We also run broader data and analytics projects where RLS is one piece of a larger governance strategy.

RLS isn't glamorous. It's the kind of thing that nobody notices when it works correctly, and everyone notices when it doesn't. But getting it right means your organisation can share Power BI reports widely without worrying about who's seeing what - and that's the whole point of self-service analytics.