There are different methods to use row level security in Power BI; You can set up Row Level Security in Power BI itself, or through a live connection from a data source such as SSAS Tabular. However row level security defined in ways mentioned in blog posts above isn’t dynamic. By dynamic row level security I mean definition of security be besides the user account information in the data source. For example when John log in to the system, based on data tables that shows John is sales manager for specific branch he should be able to see only those branch’s data. This method is possible in Power BI using DAX USERNAME() function. In this blog post I’ll 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; from Rookie to Rock Star.
For this example I will use a 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 I use on-premises data sources then half of this example should be explaining installation and configuration gateways, or if I use Azure data sources, then again I have to explain how to set up that example. So just for simplicity of this example I’ll be using data source inside Power BI.
For this example let’s create two simple tables; Sales Rep, and Transactions. Sales Rep has information of sales representatives, and transaction data is sales transactions, obviously 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.
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 structure below, and name it Transactions:
As you can see each sales transaction is handled by a sales rep. Again I mention that these tables are added inside Power BI just for simplicity of this example. Tables can come from everywhere.
load tables into Power BI, we don’t 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) to be as below;
For this example I will be using basic table visualization. The table visualization will show Date, Sales Amount (from Transactions), and Name (from Sales Rep). I also added another table visualization under that to show username, and Name (both from Sales Rep);
The main reason for this visualization is to simply shows that each user will see only their own data rows from all tables. I also will 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 value of USERNAME();
I also like to add date/time of refreshing the report with DAX NOW() function (note that NOW() function will return server’s current time, not your local. if you are interested to learn to how fetch local’s current time read here). So let’s create new measure and name it Now;
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;
USERNAME() function in DAX returns the username of logged in user. However there is a small trick for it. If we don’t set a row level security for our report. USERNAME() function will return user id which would be a unique identifier. To have an understanding of what I mean, publish your report to Power BI and browse it to see what you will see.
Without a security configuration on your report you will see unique identifier for username which isn’t useful. 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
I have explained in another post how row level security in Power BI Desktop works, so if you like to learn it more in details read this blog post. Here I will only use that technique to filter each role based on their username with DAX username() function. To create security go to 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 Sales Rep table as below;
[Username] = USERNAME()
This filter simply means that logged in user will only see his/her own records in the whole data set. As you remember the username field in Sales Rep table defined exactly as usernames of Power BI accounts. and transactions table is also related to this table based on Sales Rep ID. so filtering one table will effect 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 security setting of the data set you just published (I named this as Dynamic RLS).
And in Security tab, add all users to the Sales Rep role.
Note that adding a user here doesn’t mean that they will see data in the report. Remember that this security is dynamic, means that they will see their data rows ONLY if the underlying data set has a record for their username, and they will only see data rows related to their own 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.
Share the Dashboard
Other users should have access to the dashboard and report first to see it, so create a dashboard from main table in the report, name the dashboard as RLS (or whatever you would like to call it);
Now share the dashboard with other users
Test the Security
Now if other users open the report, and if their usernames matches one of the entries in Sales Rep table, they would see their own names, and data rows related to that in the report;
As you can see John Martin only see the transaction that he handled, and his own record in Sales Rep table. This is John’s view of the Power BI report. While my view of this report would be totally different, I will see my two transactions and my own name under Sales Rep.
You have seen how easy is to use Dynamic row level security in Power BI using DAX USERNAME() function. With this method users will see their own view of the world. However you need to make sure that your Power BI model has 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 to your data model, so keep your data model right.