Sooner or later every retail business that reports across countries hits the same request: the Polish team wants the trading report, but only with Polish numbers, and the German team the same for Germany. The tempting answer is a copy of the report per market. I have seen where that leads: eight versions of the same file, each slightly out of date, and a new measure that has to be added eight times. Row-level security solves this properly with one model, one report and a filter that depends on who is looking.
What Row-Level Security in Power BI Does
RLS stands for row-level security. It restricts which rows of the semantic model a viewer can see. You write a DAX filter on a table, for example Market[Country] = "Poland", save it as a role, and every visual, total and export for a member of that role is calculated only from the rows that pass the filter. The report layout does not change; the numbers behind it do.
The filter travels through relationships. If you secure the Market dimension, the Sales, Stock and Receipts fact tables related to it are filtered too, so the Polish team sees Polish sales, Polish stock and Polish sell-through without any extra work. This is why I always put security filters on dimension tables and never on the large fact tables: it is faster and much easier to reason about.
Should you enable row-level security at all? If different groups of viewers must not see each other’s figures, yes, and it is the only way to do it inside one report. Hiding a page or a slicer is not security: anyone with access can still reach the data through another visual, an export or Analyze in Excel. Power BI has no page-level security, so if a page must stay private, it belongs in a separate report.
Static vs Dynamic Row-Level Security for Retail Markets
There are two ways to write the roles. Static RLS hard-codes the value in each role: a role called Poland with the filter Market[Country] = "Poland", a role called Germany, and so on. Dynamic RLS uses one role whose filter reads the viewer’s login with USERPRINCIPALNAME() and looks it up in a mapping table of emails and markets.
| Aspect | Static RLS | Dynamic RLS |
|---|---|---|
| Number of roles | One per market or region | Usually one for everyone |
| Where access is managed | Role membership in the Service | A mapping table of emails and markets |
| New market | New role, republish the model | New rows in the mapping table |
| Store managers or area managers | Impractical | Works with a store-level mapping |
| Best for | A handful of markets that rarely change | Many markets, regions or stores |
My rule of thumb: up to five or six markets with stable teams, static roles are perfectly fine and easier to explain to a business owner. As soon as access goes down to region or store level, or people move between markets every season, dynamic RLS pays for itself within weeks, because access changes become an edit in a table rather than a change to the model.
Setting Up Market Roles in Power BI Row-Level Security
For a static setup, open the model in Power BI Desktop, go to Modeling → Manage roles, create a role per market and add a filter on the Market table. For dynamic RLS in a multi-market retail report, these are the steps I follow:
- Create a UserAccess table with two columns, Email and MarketCode, one row per person and market. A regional manager simply gets several rows. Keep the table in a SharePoint list or a small database table so the business can maintain it.
- Load it into the model without a relationship to the other tables and hide it from report view.
- Create one role, for example Market access, with a DAX filter on the Market dimension that keeps only the markets listed for the current user.
- If some people, such as the head of trading, need every market, give them a row with the code ALL rather than a row per market, so a new country does not need a new row for each of them.
- Publish the model, open its Security settings in the Power BI Service and add the users or, better, a security group to the role.
-- Role "Market access", table filter on Market
VAR CurrentUser = USERPRINCIPALNAME ()
VAR HasAllMarkets =
CALCULATE (
COUNTROWS ( UserAccess ),
UserAccess[Email] = CurrentUser,
UserAccess[MarketCode] = "ALL"
) > 0
VAR UserMarkets =
CALCULATETABLE (
VALUES ( UserAccess[MarketCode] ),
UserAccess[Email] = CurrentUser
)
RETURN
HasAllMarkets || Market[MarketCode] IN UserMarketsA user in two roles sees the union of both, not the overlap. That matters when you mix approaches: if someone is in a Poland role and also in a role that sees everything, they see everything. Keep role design simple and, with dynamic RLS, let the mapping table be the single place where access is decided.
If USERPRINCIPALNAME() is not working, the cause is almost always the login rather than the DAX. The user principal name is the sign-in name, which is not always the email address people write in the mapping table, and guest users from another organisation can have a different format. Add a temporary card with a measure that returns USERPRINCIPALNAME() and compare what it shows with the table.
Testing Row-Level Security with View as Role
To check row-level security in Power BI Desktop, go to Modeling → View as, tick the role and, for dynamic RLS, tick Other user and type an email from the mapping table. The whole report switches to what that person would see. In the Power BI Service, open the semantic model’s Security settings, choose the role and select Test as role; there you can also enter a specific user.
Clicking through the pages is not enough. For every role I compare a few totals with the source system, because a wrong filter often still shows plausible numbers. My checklist:
- Net sales and stock units for one market in one week, against the ERP or the sales export.
- A user with two markets, to confirm they see both and that the group total equals the sum of the two.
- A user with ALL access, whose totals must match the unsecured model.
- An email that is not in the mapping table, which must see no data at all.
- Tables that are not related to Market, such as a supplier or a size table, to make sure nothing leaks through them.
Row-Level Security Limits in Retail Reporting
RLS is reliable, but it has limits that regularly surprise retail teams. The first one is about roles in the workspace: row-level security applies only to viewers. Workspace Admins, Members and Contributors see all the data regardless of the roles. This is also the honest answer to how people “bypass” RLS: they are not bypassing it, they have edit rights. Give market teams Viewer access or distribute the report through an app.
- Once roles exist, a viewer who is not in any role sees no data. That is the safe default, but it is also the first thing new starters report as a broken dashboard.
- RLS hides rows, not tables or columns. If a market team must not see cost prices at all, you need object-level security (OLS), which is usually set up with Tabular Editor.
- A market that sees only its own rows cannot compare itself with the group average. If you want that benchmark, add a small aggregated table without a relationship to Market, knowing that this deliberately shares group-level figures.
- There is no switch to turn off row-level security. To remove it, delete the roles in Power BI Desktop and republish the model.
- Complex filters with many lookups slow every visual down. Filter small dimension tables and keep the mapping table narrow.
When I set up Power BI reporting for a brand that sells in several countries, access is part of the model design from day one, together with the KPI definitions described in my article on DAX measures for retail dashboards. Adding RLS to a finished report is possible, but designing the Market dimension with security in mind saves a lot of rework.