Row-Level Security in Power BI: Who Gets Filtered and Who Does Not

1. The fork: can this person edit the model, or only read it?

Before the explanation, one question worth answering from memory. Think of the last Power BI report you shared with somebody. Say whether that person could open the underlying model and change a measure, or whether they could only look at what you built.

That question, not the rule you wrote, decides whether row-level security does anything at all. There are two answers.

Answer one: they can only read. Their workspace role is Viewer, or they got the report through a published app. If that is true, every rule you wrote runs against every query they make, and they see only the rows their role permits.

Answer two: they can edit. Their workspace role is Admin, Member or Contributor. If that is true, row-level security does not apply to them at all. Not partially, not with a warning. They see every row in the model, and the report gives them no sign that a rule exists.

What decides between them is a dropdown in the workspace access panel, which is usually set by whoever added the person, often months earlier, and usually not by whoever wrote the security rule. That gap between the two decisions is where this fails.

It matters because the failure is silent on both sides. The person seeing too much has no idea, and the person who built the model tested it and watched it work.

2. What a role actually is

A role is a name plus one or more filters, and each filter is attached to a table. The filter is a DAX expression that gets asked about every single row and has to answer TRUE or FALSE. Microsoft's wording is worth having exactly: "A DAX filter evaluates TRUE/FALSE for each row. Only rows that return TRUE are visible. Everything else is completely removed."

Completely removed is the part to hold on to. This is not a report filter that a user can clear, and it is not a default someone can override in the Filters pane. The rows are gone before the visual is drawn.

The simplest useful rule, typed into the DAX editor under Modeling › Manage roles, is a comparison:

[Region] = "East"

Say out loud what that returns for order 1004, which is a North lamp. FALSE, so the row is dropped. Order 1013 is an East lamp, so TRUE, so the row stays. Sixteen questions, four answers of TRUE, and the model an East role member queries has four rows in Orders and totals 3,040.

One naming rule that catches people, and it is documented rather than folklore: a role name cannot contain a comma. London,ParisRole is rejected.

Filters land on one table and then travel. RLS filters propagate through the model's relationships the same way any other filter does, which is why it is usually better to put the rule on a small dimension table and let it flow to the fact table. That is one more argument for a proper star schema, and Microsoft's guidance says so directly: "it's often more efficient to enforce RLS filters on dimension tables, and not fact tables."

3. Two tools, two steps: where roles live and where people do

This trips up more exam candidates than any other part of the topic, and the reason is that the job is split across two different products.

You cannot assign users to a role in Desktop. The option is not hidden or buried, it is not there, because Desktop has no idea who is in your organization. And the Security page in the Service only appears for models that already have roles defined, so a model with no roles gives you nothing to click and no explanation.

Two smaller facts that come up. Only the semantic model's owner or a workspace admin can add members to roles. And Microsoft 365 groups cannot be used for role membership at all; security groups, distribution groups and mail-enabled groups can.

Anyone who has published a model but not assigned anybody gets the outcome that looks like a bug and is not. A user in no role at all typically sees nothing rather than everything, because the rule is enforced and no role permits any row.

4. The failure: the East manager who sees 9,890

Here it is worked end to end, and it is the version that actually happens.

Priya runs the East region. You build the model, write [Region] = "East" into a role called East, publish it, add Priya to the role in the Service, and test it. The test passes: four orders, 3,040.

Some weeks earlier, though, Priya was added to the workspace so she could open the other three reports the team keeps there. She was given Member. Nobody connected the two decisions, because they were made by different people for different reasons on different days.

Priya opens her regional card. It reads 9,890.

Before reading on, say why the report gives her no clue. The rule is still there, the role is still assigned, and she is still a member of it.

Because there is nothing to show. RLS does not grey anything out or add a banner. It removes rows before the query returns, so a report with no rows removed is identical to a report that never had a rule. Priya's card is not displaying an error. It is displaying the correct total for the rows she was given, and she was given all of them.

The fix is one dropdown, and it is not in the model. In the workspace access panel, change Priya from Member to Viewer. She keeps the reports, loses nothing she uses, and the rule starts running. If she needs to build her own reports on top of the model, give her Viewer plus Build permission on the semantic model, because RLS still applies to Viewers who have Build. Microsoft states that case explicitly, including for Analyze in Excel.

Now picture this on a workspace you actually own. Open the access list and read the roles rather than the names. Say out loud how many people on it could edit, and whether every one of them is supposed to see every row. That count is the real scope of your security rule.

5. Two roles is a union, not an intersection

Before the explanation, a prediction. Somebody is in a role that permits East, and you also add them to a role that permits lamps. Say whether they end up seeing East lamps only, or something larger.

Something larger, and it is the answer most people get wrong because every other permission system they have used behaves the other way.

Microsoft's guidance is unambiguous: "When a report user is assigned to multiple roles, RLS filters become additive. It means report users can see table rows that represent the union of those filters." And the sentence right after it is the one to memorize, because it names the assumption being broken: "unlike permissions applied to SQL Server database objects (and other permission models), the 'once denied always denied' principle doesn't apply."

Worked on the fixture. The East role permits orders 1003, 1006, 1010 and 1013. A Lamps role permits every lamp order, which is 1004, 1008, 1009 and 1013.

Expected 600, delivered 4,040, and 4,040 minus 600 = 3,440 of rows nobody meant to hand over. Adding a second role widened the access, which is the opposite of what "adding a restriction" sounds like.

Microsoft's own illustration of this is starker than mine and worth keeping in your head as the extreme case. A Workers role restricts a Payroll table with the rule FALSE(), so it returns no rows at all. A Managers role uses TRUE(), so it returns everything. Somebody who maps to both roles sees the entire Payroll table. The deny does not win. There is no deny.

The fix is a design rule rather than a setting: one person, one role. If a person needs East and lamps together, build a single role that carries both filters, one on each table, so both conditions apply at once. Microsoft's guidance recommends exactly this, and the reason it gives is worth noting: a user can end up in a second role indirectly, through a security group, without anybody deciding to put them there.

6. Dynamic RLS, and the typo that opens the whole table

Writing one role per region does not scale past a handful. Dynamic RLS is one role for everybody, where the rule compares a column to whoever is signed in.

You need a small mapping table, one row per person, with their sign-in address and what they are allowed to see. Then the rule goes on that table:

[UserEmail] = USERPRINCIPALNAME()

USERPRINCIPALNAME takes no arguments and returns the user principal name at connection time, which looks like an email address. The filter lands on the mapping table, the mapping table is related to the region, and the filter travels down that relationship to Orders. One role, one rule, and adding a new manager becomes a row in a table rather than a change to the model.

There is an older function, USERNAME, and the difference is a real trap rather than trivia. In Power BI Desktop, USERNAME returns DOMAIN\User. In the Power BI Service, USERNAME and USERPRINCIPALNAME both return the user principal name. So a rule built and tested against a domain-style value in Desktop can behave differently once published. Use USERPRINCIPALNAME and store matching values in the mapping table.

Now the part worth the whole section. This pattern, which reads perfectly sensibly, hands over the entire table to a typo:

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

Workers see internal rows. Everybody else, meaning managers, sees everything. But "everybody else" is not a list of managers. It is every value that is not exactly the string Worker, so Wrker falls into the else branch and returns TRUE for every row. Microsoft's guidance uses this exact example, and its fix is to test for each expected value and make the fall-through deny:

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

The habit underneath it: in a security rule, the last branch is the one that runs when you have not thought of the case. Make it return nothing.

7. What View As proves, and what it does not

Desktop has View As on the Modeling ribbon, and the Service has Test as role on the model's Security page. Both are genuinely useful and both prove less than people think.

What they prove: your DAX filter is valid, it lands on the table you meant, and it propagates through the relationships you expect. That is most of the work, and testing every role this way is not optional.

What they do not prove, and this is documented rather than folklore:

• They cannot tell you a person's workspace role. The whole failure in section 4 is invisible to both tools, because both simulate the rule and neither simulates the bypass.
• For dynamic RLS, the test uses your own identity. USERPRINCIPALNAME returns your sign-in name, not the one you are trying to simulate. So a passing test tells you the rule works for you.
• Not every surface is covered. Test as role does not work for paginated reports, and it does not validate Q&A visuals, Quick insights or Copilot. It also does not work for DirectQuery models with single sign-on.

The test that closes the gap is unglamorous and takes two minutes: put a card on the report showing USERPRINCIPALNAME(), publish, and have one real person in each audience open it and tell you what it says next to their totals. Microsoft's troubleshooting guidance suggests the same measure, named something like "Who Am I". A real user's session is the only thing that exercises both halves at once.

8. Four things RLS does not do

Each of these is somebody's disappointed afternoon.

It does not hide columns or measures. Microsoft is direct about it: RLS "filters table rows. They can't be configured to restrict access to model objects, including tables, columns, or measures." If a person can see a row, they can see every column on that row, salary included. The feature for hiding a column is object-level security, and it is separate.

It does not let people see a total without the detail. Asked whether RLS can hide detailed rows while still showing them in a summary, the answer is no: you secure rows, and users can see either the details or the summary of what they have. Getting a company-wide total next to a personal one needs a separate summary table built for that purpose, not a cleverer rule.

It cannot be overridden by DAX, in either direction. This one cuts both ways and is worth knowing. No measure can escape RLS, and Microsoft goes further: a DAX expression "can't even determine that RLS is enforced". So there is no writing a smarter measure to work around it, and equally no accidentally punching a hole in it with ALL or REMOVEFILTERS. If you want the mechanics of what a measure can and cannot see, that is CALCULATE and filter context.

It does not follow the model everywhere. Live connections to Analysis Services enforce security in that model rather than in Power BI, and the Security option does not even appear. Publish to web does not work with RLS at all. Service principals cannot be added to a role, so an app authenticating that way is not filtered by one.