How to Use Dynamic Row-Level Security with Power BI

There are different methods in Power BI for using row-level security; either you can set up Row-Level Security in Power BI itself, or you can set up a live link from a data source such as SSAS Tabular. Nevertheless, row-level security defined in the ways suggested in the following blog posts is not dynamic. By dynamic row-level security, we mean the definition of security to be in the data source next to the user account information. For example, when John logs in to the system, based on data tables showing John is a sales manager for the particular branch, he should be able to see only the data from those branches.This method is possible in Power BI using DAX UserName() or UserPrincipalName() function. In this blog post, we will show you an example of dynamic row-level security with DAX USERNAME() function in Power BI. If you like to learn more about Power BI, read Power BI online book.

2016-07-04_23h10_10Why Dynamic Row-Level Security?

The most important question is why dynamic row-level security? To answer this question, you need to think about the limitation of static row-level security. Statics row-level security is simple to implement; however, if you have thousands of roles, then it would be a nightmare to maintain. For example, if you want to create a payroll Power BI report, in a company with 10,000 users, you want every user to have his/her role. Dynamic row-level security is the answer to such scenarios.

Sample Data

For this example, we will use data entered in Power BI itself. There won’t be any external data sources. This doesn’t mean that dynamic security has an issue with external data sources. Dynamic security works with any data sources as long as we have related data rows in the tables. However, if we use on-premises data sources, then half of this example should be explaining installation and configuration gateways, or if we use Azure data sources, then again, we have to explain how to set up that example. So just for the simplicity of this example, we will be using data sources inside Power BI.

For this example, let’s create two simple tables; Sales Rep, and Transactions. Sales Rep has information from sales representatives, and transaction data is sales transactions — each sales transaction handled by a sales rep. So let’s create sample tables in Power BI.  Open Power BI Desktop and from External Data section choose Enter Data.

2016-07-04_22h23_42

Create the Table

Create a table as above with three columns and data in it. You have to use usernames similar to Power BI accounts that you want to set up security for it. Name this table as Sales Rep.

Create another table for Transactions with the structure below, and name it Transactions:

2016-07-04_22h28_08

As you can see in the above screenshot, each sales transaction is handled by a sales rep. Do note that these tables are added inside Power BI just for the simplicity of this example. Tables can come from everywhere.

Load tables into Power BI, we do not need to do anything with Power Query at this stage. Go to Relationship tab and verify the relationship between Sales Rep (ID) and Transactions (Sales Rep) as shown below:

2016-07-04_22h30_55

Sample Report

For this example, we will be using basic table visualization. The table visualization will show Date, Sales Amount (from Transactions), and Name (from Sales Rep). We have also added another table visualization under that to show username, and Name (both from Sales Rep);

2016-07-04_22h34_45

The main reason for this visualization is to show that each user will see only their data rows from all tables. We will also add a measure for USERNAME() in DAX to see the user logged in from my report. So in Data Tab, create a new measure and name it user, with a value of USERNAME().

2016-07-04_22h37_30

We would also like to add date/time of refreshing the report with DAX NOW() function (note that NOW() function will return the server’s current time, not the local). So let’s create a new measure and name it Now.

2016-07-04_22h41_52

Now let’s add two other table visualizations to the report. One for User, and another for Now. Here is the report’s final view;

2016-07-04_22h43_20

DAX Functions: UserName() and UserPrincipalName()

USERNAME() function in DAX returns the username of logged in user. However, there is a small trick for it. If we don’t set up row-level security for our report, the USERNAME() function will return user id, which would be a unique identifier. To have an understanding of what we mean, publish your report to Power BI and browse it to see what you will see.

2016-07-04_22h50_04

Without a security configuration on your report, you will see a unique identifier for the username, which isn’t useful. Now let’s set up row-level security and assign users to it to see how it works.

UserPrincipalName() function in DAX works exactly like UserName() function with the difference that it will always return the username (not the unique identifier). So basically, UserPrincipalName() is a better function for testing, but the works both the same in a production environment. Now let’s set up row-level security and assign users to it to see how it works.

Row-Level Security in Power BI Desktop

Here we will only use that technique to filter each role based on their username with DAX username() function.  To create security, go to the Modeling tab (you need Power BI at least June 2016 update for this example), Manage Roles. Create a role and name it Sales Rep. and define a filter on the Sales Rep table as below.

2016-07-04_22h54_07

This filter means that logged in users will only see his/her records in the whole data set. As you remember, the username field in the Sales Rep table defined as usernames of Power BI accounts. The transactions table is also related to this table based on Sales Rep ID. So filtering one table will affect others. As a result, this single line filter will enable dynamic row-level security in the whole Power BI solution here.

Assign Users to Power BI Security

Now Save, and publish your solution to Power BI. In Power BI service, go to the security setting of the data set you just published (we have named this as Dynamic RLS).

2016-07-04_22h59_14

And in the Security tab, add all users to the Sales Rep role.

2016-07-04_23h01_24

Note that adding a user here doesn’t mean that they will see data in the report. Remember that this security is dynamic, which means that they will see their data rows ONLY if the underlying data set has a record for their username. They will only see data rows related to their username, not others.

Now, if you refresh the report in Power BI, you will see actual usernames. Because we already set up security for it, so it doesn’t show unique identifiers anymore.

2016-07-04_23h04_26

Share the Dashboard

Other users should have access to the dashboard and report first to see it. Create a dashboard from the main table in the report, name the dashboard as RLS (or whatever you would like to call it).

2016-07-04_23h06_14

Now share the dashboard with other users:

2016-07-04_23h07_49

Test the Security

Now, if other users open the report, and if their usernames match one of the entries in the Sales Rep table, they would see their names, and data rows related to that in the report.

2016-07-04_23h10_10

As you can see, John Martin only sees the transaction that he handled and his record in the Sales Rep table. The screenshot showed above is John’s view of the Power BI report. While my view of this report would be different, we will see the two transactions and our name under Sales Rep.

Summary

You have seen how easy it is to use Dynamic row-level security in Power BI using DAX USERNAME() or UserPrincipalName() function. With this method, users will see their view of the world. However, you need to make sure that your Power BI model has a relationship set up properly. Otherwise, people might see other table’s data when there is no relationship between their profile table to those tables. Dynamic row-level security is highly dependent on your data model, so keep your data model right.

Leave a Comment