How to Setup Static and Dynamic Row Level Security (RLS)
in Power BI?

BI Connector Team |

Power BI steps to setup row level security rls

Row Level Security (RLS), simply put, is the way we restrict data presented in a report based on user login.

The complexity of the RLS set up varies depending on the number of dimensions and dimension members that require security.

If you have a small, fixed list of users and a static number of dimensions, then a basic RLS setup will work. This approach involves creating roles for different user types and then applying filters to the desired dimension tables. 

Power BI DAX – Difference Between Concatenate() and Concatenatex() >>

However, if your user list is longer and your dimensions are subject to changes, you will need the dynamic approach.

Static RLS setup in Power BI

Let’s start with a static example. Consider the scenario where you would like to secure the country table to one country per user. You would go through the following steps:

  1. On the ‘Modeling’ tab in the Power BI toolbar click on “Manage roles”.
Manage Roles in Power BI
  1. Create a new role, select the table which you want to get filtered based on the role, and then create a True/False statement to filter this table with.
Create roles and filters

Snowflake For Data Lake Analytics – Jumpstart Guide >>

  1. Now that the role has been created test the functionality using the ‘View as’ button.
View report as user
  1. Check the box next to the role you would like to test.
select roles
  1. You are now impersonating the role (notice the yellow warning bar indicating the current role being applied). 
Report result when viewed as a role

Supercharge OBIEE (and OAC/OAS) With Power BI [eBook] >>

  1. Repeat these steps for every desired role.
  2. After you have uploaded this report to the Power BI portal, go to the Security settings for the data source to assign Users to the desired roles.
Select more options
Select Security
Enter user email for role

If you have a longer list of users or more dimension changes then you will likely want to manage your roles and users in a more dynamic fashion. We list out the steps for setting this up in the next section.

Dynamic RLS setup in Power BI

Rather than assigning users to roles in the Power BI portal, we can keep a list of users externally (in a database or simply in a spreadsheet) and use the USERNAME() or USERPRINCIPALNAME() functions to filter the secured data tables.

[Open Secret] Reference Architecture for Power BI OBIEE/OAC Integration >>

Let’s see the steps now!

  1. Determine different user roles. 

For instance, for general users, we want a role to secure the data for countries by continent. So, in the case of our sample data, there should be a mapping of users to North America and Europe.

If the user is a “VP” we will be granting them full access.

The ‘Users’ and ‘Roles’ tables will look something like this:

Users and Roles
Roles and Countries

  1. Setup Table Relationships

Link the tables together and then to the fact table as pictured:

Users[Role] 🡪 Roles[Role]

Roles[Country] 🡪 financials[Country]

Power BI Data Relationships
  1.  Manage Roles

Create a new role. Select the ‘Users’ table and create a True/False statement to filter this table with. In this instance, we will be using the function USERPRINCIPALNAME(). This function returns the email address associated with the Power BI Portal login of the individual user. 

Create roles and filters
  1. Test Roles

Now that the role has been created test the functionality using the ‘View as’ button.

Power BI view as user

Select the role you want to test (Security) and also the User you want to test (User2)

Select roles for viewing as

Because User2 is set up to view European countries only we immediately see the data filtered to just France and Germany.

Report viewed as role

From now on whenever you make changes to the user table and or roles table that is connected to this report it will flow through automatically. No need to go in and individually select each user to assign to each role.

Further Complex Scenario

Now, for added complexity, let’s explore securing more than one dimension dynamically using more advanced DAX.

Let’s say we want to assign our roles as defined different business Segments.

The 5 Must-haves of Power BI OBIEE Connectors [Infographic] >>

We can assign a list of segments to individual users, and filter the segment values using the CONTAINSSTRING() function.

  1. We add a column, RoleSegment column to our Users table. This can be populated using a LOOKUP() function on RoleCountry to a separate table, or customized manually for each user. The best practice is to use the table lookup method.
Users countries segments
Segments and Roles
  1. In this case, we have a seperate Segment table connect to our main fact table. There is no need to setup a direct connection to this table since we will be filtering the security roles using DAX. The schema will look something like this:
Data relationship
  1. In the ‘Manage roles’ wizard (used earlier) implement the following DAX on the Segment Table on the Security Role.
create roles

[Whitepaper] How to Securely Connect Power BI to OBIEE and OAC? >>

This DAX will filter the segment table down to any members that are contained in the comma-separated list in the RoleSegment field above. Now when we imitate User3 we see not only the countries filtered to North America alone, but also the Segments limited to those in the list for “GeneralNA”.

View report as role

Using these two methods as a guide you will be able to set up security for as many dimensions as you see fit within a single data model.