What happens when a User has Multiple RLS Roles in Power BI?

Row-level security (RLS) in Power BI allows restricting what data users can see via boolean DAX expressions; this is done per dataset.  I never thought much about what it meant for a user to be assigned multiple roles until it came up in a meeting, in which I quickly declared “it would be the AND union of the conditions of each of the roles.”  One of the attendees, just as swiftly, responded that he thought it would be the most flexible combination, meaning an OR union of the conditions.  So which one is it?

To my surprise, I couldn’t find an article online that explained which one it was.

Let’s quickly review RLS.  The process to define RLS for a dataset is more or less as follows (for a full tutorial see https://docs.microsoft.com/en-us/power-bi/service-admin-rls):

  1. A role is created in Power BI Desktop and defined by declaring one or more boolean DAX expressions against the dataset.  For example, Customers[Gender] = “M”.  This would restrict members of that role to see only male customers.   Moreover, that same role could have another expression associated with it, such as Geography[Country] = “Canada”.  The conditions are AND-ed and members of the role would be able to see only male Canadian customers.
  2. Additional roles are created in the same fashion, as necessary.
  3. The report is published to the Power BI Service.
  4. A user with permissions to the workspace accesses the dataset’s security page and assigns each intended user to one or more roles.

So what happens when a user is assigned to two roles?  Is the data filtered for the user by OR-ing the expressions of each role or by AND-ing the expressions of each role?  That is, is it

Role1Condition1 AND Role1Condition2 AND … )
OR ( Role2Condition1 AND Role2Condition2 AND … )

or is it

Role1Condition1 AND Role1Condition2 AND … )
AND ( Role2Condition1 AND Role2Condition2 AND … )

The answer is the first expression, the least restrictive.  See the attached Power BI report (Multiple Roles) if you want to experiment yourself with roles and sample data.

For real-world context, this came up when discussing how to secure a workspace and a dataset in light of 150 users and two roles: one with full access to the data and another with partial access to the data.  The 150 would split at about 5 for the first role and 145 for the second.  We needed to give access to the app to all 150 and then assign users to roles.  We were wondering how many AD security groups we needed to create and manage.

If the conditions for the roles were AND-ed as I initially thought, we were going to need one group for the 150 to access the app and another group for the 145 in the more restrictive role.  The 5 users for the “full” role would be entered individually.

However, if the conditions for the roles were OR-ed, as is the case, we would only need one group for the 150.  The partial role would have all 150 users and the 5 users for the “full” role would be entered individually.  The OR logic would automatically give full access to the 5.

P.S.  How do you define a role with full data access?  Just create the role but don’t add any DAX expressions for it.

Download the sample report here: Multiple Roles.

Please follow and like us:


Leave a Reply

Your email address will not be published. Required fields are marked *