Labs · Power BI

Lab: Add Row-Level Security So Each City Manager Sees Only Their City, and Test It with View As

By Ravindra Bagale · Last updated: 8 October 2026

Suppose we are…

Suppose Croma shares one Maharashtra sales report with four city managers. Company policy: a manager may see only their own city's sales. pbi_cities.csv already has a ManagerEmail column (pune.manager@example.com and so on). We add row-level security (RLS): a rule on the city table that keeps only the row whose ManagerEmail matches the signed-in user. Because pbi_cities filters pbi_sales through the relationship, the manager's sales are filtered too. The data and emails are sample data made up for practice (example.com is a reserved test domain).

Goal of this lab

By the end you will have:

• A dynamic role City Manager with the rule [ManagerEmail] = USERPRINCIPALNAME().
• A static role Pune only with the rule [City] = "Pune".
• Tested both with Modeling → View as and seen only Pune's 346,000.

What you need (all free)

• Power BI Desktop and a star-schema file from Lab 11 or later.
• Sample data if you need to rebuild: Download pbi_sales.csv (24 orders) Download pbi_cities.csv (with ManagerEmail) Download pbi_products.csv
• 35 minutes.

The data: before and after

Before. Everyone sees all four cities.

After. Viewing as the Pune manager: one row, Pune 346,000.

The formula

The dynamic rule on table pbi_cities:

[ManagerEmail] = USERPRINCIPALNAME ()

• USERPRINCIPALNAME () returns the email of the person viewing the report (in the Service, their sign-in name).
• The rule is checked on every row of pbi_cities. Only rows where it is TRUE stay visible. For pune.manager@example.com that is one row: Pune.
• The relationship pbi_cities (1) → pbi_sales (many) then keeps only Pune's orders. Total Sales = 346,000.

The static rule, for comparison:

[City] = "Pune"

Static roles need one role per city (four roles, four rules). The dynamic role needs one role for everyone, and a new city only needs a new row in pbi_cities.

Steps

1. Open your file and Save as Lab-26-rls. Make a Table with pbi_cities[City], pbi_cities[ManagerEmail] and Total Sales, and a Card with Total Sales.

   👁 What you should see: 4 rows and 1,302,000, as in the Before image.

2. Click Modeling → Manage roles → New. Name the role City Manager.

3. Under Select tables, choose pbi_cities. Click Switch to DAX editor, type [ManagerEmail] = USERPRINCIPALNAME () and click Save.
4. Click New again: name Pune only, table pbi_cities, DAX rule [City] = "Pune". Save and close the window.

5. Click Modeling → View as. Tick Other user and type pune.manager@example.com. Tick the role City Manager. Click OK.

   👁 What you should see: a yellow bar "Now viewing as: pune.manager@example.com, City Manager". The table shows one row, Pune, and the card shows 346K.

6. Look at a visual by Product (or add one: pbi_products[Product] with Total Sales).

   👁 What you should see: all four product names, but only Pune's sales: Laptop 200,000, Mobile 105,000, Smartwatch 25,000, Headphones 16,000. RLS filtered the sales, not the product list.

7. Click Stop viewing. Then View as again, untick Other user, tick only Pune only.

   👁 What you should see: the same Pune result from the static rule.

8. View as once more with Other user = someone@example.com and role City Manager.

   👁 What you should see: the table is empty and the card shows (Blank): an unknown email sees nothing. That is the safe default.

9. Click Stop viewing and save.

Common mistakes

Self-check checklist

Try-at-home challenge

The regional head of Western Maharashtra and Konkan must see two cities, Mumbai and Pune. Without adding a second email column to pbi_cities, how would you support managers who own more than one city?

Samjla ka? One dynamic rule on the dimension table, USERPRINCIPALNAME() for the viewer, and test with View as. Aata pudhe jaauya: find the slowest visual with Performance Analyzer and make it faster.