Skip to main content

How to Add Row-Level Security Roles in Power BI in the Browser

· 6 min read
Jagdish Kumawat
Founder @ Dewiride

Row-level security (RLS) shows each person only the rows they may see. You define roles, each with a filter rule on a table, and later add people to them. In the Power BI service, you can create the roles in the browser. Here's how, with a rule that keeps each person's own rows.

Microsoft's row-level security article describes rules as DAX expressions that return true or false for each row: only rows that return TRUE are visible. Its web modeling article lists the steps in the browser: Manage roles, New, the role's name, the table, and the filter. The example model holds expense claims (How to Create a Semantic Model Without a Report in Power BI) and gets two roles:

RoleTableRuleWho sees what
Own expensesExpenses[SubmitterEmail] = USERPRINCIPALNAME()Each person, their own claims
Category managersCategory managers[ManagerEmail] = USERPRINCIPALNAME()Each manager, the claims of their categories

USERPRINCIPALNAME() returns the signed-in user (How to Get the Signed-In User in Power BI with USERPRINCIPALNAME).

Step 1: Open Manage Roles​

Open the model in Editing mode and select Manage roles in the Security group of the Home tab:

The model editor's Home tab with the groups Data, Queries, Calculations, Parameters, Security, Relationships, Model health and Explore, and Manage roles outlined in red in the Security group

Step 2: Create the Role and Pick Its Table​

The Manage security roles dialog opens on its Create tab. Under Roles, select New. A role called Untitled appears; double-click it, type Own expenses and press Enter. Under Tables, select the table the rule filters, Expenses:

The Manage security roles dialog on the Create tab with the role Own expenses, the tables Category managers, Expense Categories and Expenses with Expenses selected, an empty Rules area, and the Switch to DAX editor button outlined in red

Step 3: Write the Rule in DAX​

The default editor builds simple rules from drop-downs. A rule that uses USERPRINCIPALNAME() needs the DAX editor: the article says dynamic rules can only be defined using DAX. Select Switch to DAX editor and enter:

Own expenses → Expenses
[SubmitterEmail] = USERPRINCIPALNAME()
  • [SubmitterEmail] is the column of Expenses with each claim's submitter.
  • = USERPRINCIPALNAME() is true only on the signed-in person's rows.

The DAX editor of the Own expenses role on the Expenses table with the rule [SubmitterEmail] = USERPRINCIPALNAME(), and a note that the filter is a DAX expression that returns a True/False value

Select the tick above the editor to check the expression, then Save.

Step 4: Add the Second Role​

Repeat for Category managers, on the Category managers table:

Category managers → Category managers
[ManagerEmail] = USERPRINCIPALNAME()

After Save, the dialog said Successfully applied role changes.:

The Manage security roles dialog with the message Successfully applied role changes., the roles Category managers and Own expenses, and the Category managers table's rule [ManagerEmail] = USERPRINCIPALNAME()

This rule filters Category managers, not Expenses. For it to reach the claims, the relationship between the tables must carry security filters both ways (How to Make a Relationship Filter in Both Directions in Power BI).

The roles have no members yet. Add people or groups next (How to Add Members to a Row-Level Security Role in Power BI), then test them (How to Test Row-Level Security in Power BI).

Common Mistakes​

MistakeWhat happensDo this instead
Putting the rule on the wrong tableThe filter never reaches the rows you meantPut it on the table that holds the emails
Comparing with names instead of emailsNothing matches the signed-in userCompare with an email column
Expecting the role to apply to workspace membersAdmins, Members and Contributors see everythingRLS applies to Viewers
A role with no rule by mistakeThe article says a role with no rules gives access to all rowsCheck every role has its rule, unless that's the point

Troubleshooting​

SymptomLikely causeFix
Manage roles is greyed outThe model is in Viewing modeSwitch to Editing
The tick shows an errorA column name doesn't exist on that tableCheck the name in brackets
A tester sees all rowsThey're an Admin, Member or ContributorTest with Test as role, or as a Viewer

FAQ​

Can a role have no rule at all?​

Yes, and that's useful. Microsoft's security planning article says it's possible to define a role that has no rules, and that the role provides access to all rows of all model tables: right for people allowed to see everything.

Are roles combined?​

Yes: someone in two roles sees the rows of both.

Conclusion​

Manage roles > New, name the role, pick its table, Switch to DAX editor, write the rule, and Save. The example's Own expenses and Category managers compare an email column with USERPRINCIPALNAME().

Additional Resources​