How to set up row level security so each branch sees its own numbers

Start on paper with every role that opens the report: branch managers, regional heads, finance and the owner, each with the branches they may see. Then mark the figures that stay hidden. Purchase cost and margin are usual candidates.

Look for the awkward cases early. A regional head covers four branches, a finance executive sees every branch but not payroll, and a branch manager who moves between sites keeps changing rows in the map. Each of these shapes the mapping table.

Row level security hides rows. It does not hide columns. If purchase cost must stay hidden from a branch, leave that column out of their report or apply object level security to the field.

Create a small table. It needs two columns: the user’s sign-in email and the branch code; one row per pairing, so the regional head has four rows. Keep it in a place finance can edit, such as a managed sheet or a table in the warehouse, so nobody waits for a developer when a manager moves.

Use the one branch code found in the sales, stock and ledger tables, and if one system calls a branch BR04 while another says Northgate Main, clean that first. Otherwise the filter returns nothing. Every manager then opens a blank page.

Add the map to the model and relate it to the branch dimension, because sales, stock and receivables all hang from that dimension and one filter reaches every table. Relationships do the rest.

In Power BI Desktop, open Manage Roles and create a role named Branch, then pick the branch dimension to hold the rule. Add a DAX filter that keeps rows whose branch code appears in the map for the signed-in user, which USERPRINCIPALNAME returns. Save the role.

Put the rule on the dimension, not on each report page, so that every new visual inherits it without anyone remembering to copy anything. A rule copied across ten tables gives ten chances to forget one, and the forgotten table leaks a branch’s margin to the wrong manager.

Add an unfiltered role for head office. Assign people to roles in the service through an Entra security group, so a new joiner needs one change, not five separate grants across workspaces.

Use View as, pick the Branch role and type a manager’s email, then check totals against the source for that branch. Check every page that carries stock, receivables or targets, because sales alone can look right while a table with no relationship to the branch dimension shows every row.

Then test the failures. A user who is not in the map should see nothing, not everything; a regional head should see exactly four branches. Try a manager with two roles. Access is combined across roles, so a stray head office role quietly widens what that manager can see on every page of the report.

Ask one real branch manager to log in and read the report with you, sitting beside them without touching the mouse. Watch where they hesitate. A missing target line, or a stock figure that looks too low against the last physical count, deserves chasing before the launch email goes out.

Do not give head office the branch report with a switch; publish a second version or a separate page set that uses the unfiltered role. That keeps the branch version clean and stops one mistaken assignment from exposing consolidated margin to a whole workspace of branch managers who were never meant to see it.

Document the setup, meaning the roles, the map table, its owner and the steps to add a branch, and keep the page where the next analyst will find it. Review the map every quarter against the HR staff list, removing anyone who has left and rechecking anyone who changed city.

Write the test list down. Run it after every model change. Run it again for every new branch, and keep the dated results beside the access matrix.