Level 4 · A row-level security role for the West

Row-level security (RLS) hides rows of data from people who shouldn’t see them. You define roles in Power BI Desktop; each role holds a rule per table, a DAX expression that returns TRUE or FALSE for every row. Members of the role see only the rows where the rule says TRUE, and every visual, total and export follows.

What you are working in

This page is a practice tool, not Power BI. You write the rule for one role on the customers table; the engine runs it on every customer, then fills the visual with what each member of the role would see: their Sales and their number of Customers. It follows Power BI’s rules, so a rule that works here works there.

To do it for real in Power BI Desktop: Modeling → Manage roles → New, name the role West, select the customers table, and either build the filter in the drop-down editor (state is one of CA, OR, WA) or select Switch to DAX editor and type the rule below. Press Save. To check it, Modeling → View as, tick West, OK: the whole report now shows what the role sees, with a yellow bar until you Stop viewing.

The rule

customers[state] IN {"CA", "OR", "WA"}

IN { … } tests whether the value is one of a list. It is shorter and clearer than chaining ORs:

customers[state] = "CA" || customers[state] = "OR" || customers[state] = "WA"

Both keep the same rows. Text comparisons in DAX ignore case, so "ca" would match too.

One rule, every table

You only filter customers, yet Sales shrinks as well. A security filter behaves like a filter from a slicer that nobody can clear: it flows from the one side of each relationship to the many side. Customers filters orders, orders filters order lines, and every measure built on them sees West data only. That is why rules belong on small dimension tables: one short rule, and the star schema carries it everywhere.

The opposite direction is not filtered by default. A West member still sees all 120 products in the product list, because products sits on the one side of its relationship with order_items. Usually that is fine (product names aren’t secret); if it isn’t, the products table needs its own rule.

Roles are not people

Desktop defines what each role can see. It doesn’t decide who is in it: you add people and groups to roles in the Service after publishing (a later level). A static role like this one needs a role per region, so with ten regions you maintain ten roles and ten lists. The next level replaces them with one dynamic rule.

Common mistakes

• A value, not a test. customers[state] on its own isn’t TRUE or FALSE.
• Rules on the fact table. Filtering order_items row by row works, but it is slower and leaves customers and orders unfiltered.
• Testing as yourself. As the report’s author you see everything; use View as.