Power BI team released the feature for creating Row Level Security in Power BI website in March, and I’ve written a blog post about how to use it. In June’s update for Power BI Desktop, this feature took an step further, and is available in the Power BI Desktop. Yes, Row level security configuration is now part of your Power BI model, and will be deployed with your model. It won’t be over-write with every publish from Power BI Desktop. In this post I’ll show you this feature in details. If you like to learn more about Power BI, read Power BI online book; from Rookie to Rock Star.
What’s Good About Row Level Security in Power BI Desktop?
Row Level security is about applying security on a data row level. For example sales manager of united states, should only see data for United States and not for the Europe. Sales Manager of Europe won’t be able to see sales of Australia or United States. And someone from board of directors can see everything. Row Level Security is a feature that is still in preview mode, and it was available in Power BI service, here I mentioned how to use it in the service. However big limitation that I mentioned in that post was that with every update of the report or data set from Power BI Desktop, or in other words with every publish from Power BI Desktop, the whole row level security will be wiped out. The reason was that Row Level Security wasn’t part of Power BI model. Now in the new version of Power BI Desktop, the security configuration is part of the model, and will be deployed with the model.
For this example I will use AdventureWorksDW2014 sample database. You can download it from here and install it. Also download June edition of Power BI Desktop.
Create Sample Report
Let’s start with creating a sample report in Power BI Desktop from AdventureWorksDW2014 database. I only select DimSalesTerritory, and FactResellerSales for this example;
without any changes in Power Query editor, let’s load it in the report, and build a simple column chart with Sales Amount (from FactResellerSales), and Country (from DimSalesTerritory).
The chart shows sales amount by countries, which can be used for creating row level security on Geo-location information easily. Now let’s add one card visualization for total Sales Amount. and two slicers (one for Sales Territory Group, and the other one for Sales Territory Region). This is the shape of this sample report now;
Our grand total Reseller sales amount in this view is $80M, and we have sales values for Australia, Canada, France, Germany, UK, and USA. Now let’s create roles.
Now that we’ve build a visualization sample, let’s create roles for that. our goal here is to build roles for sales manager of USA, and Europe. they should each only see their own group or country in the data set. For creating roles go to Modeling tab in Power BI Desktop. You will see a section named Security there (This is just added in June’s update of this product);
Click on Manage Roles to create a new role. You will see Manage Roles window which has three panes as below
You can create or delete roles in numbered 1 pane, You can see tables in your model in numbered 2 pane (for this example you will see two tables only, but not now, after creating the first role), and then you can write your DAX filtering expression in numbered 3 pane. Yes, you have to write DAX code to filter data for each role, but this can be very simple DAX expressions.
Now Create a Role, and name it as “USA Sales Manager”, you will see two tables in the Tables section: FactResellerSales, and DimSalesTerritory. with click on ellipsis button of each table you can create DAX filters for each column. from DimSalesTerritory create a filter for Country.
Now in the DAX Filter expression you will see an expression created automatically as [SalesTerritoryCountry] = “Value”, change the value to United States, and apply.
Now create another role, name it as Europe Sales Manager, put filter on SelesTerritoryGroup this time, and change Value to “Europe” as below;
Testing Roles in Desktop
Great, we have created our two sample roles. now let’s test them here. Yes, we can test them in Power BI Desktop with View As Roles menu option. This option allows us to view the report exactly as the user with this role will see. we can even combine multiple roles to see consolidated view of a person who has multiple roles. Go to Modeling tab, and choose View As Role option.
Choose Europe Sales Manager, and click on OK. You will see sales for Europe only showing with grand total of $11M, and showing only countries Germany, UK, and France.
You can also see in the top of report there is an information line highlighted showing that the view is Europe Sales Manager. If you click stop viewing you will see the report as normal view (grand total view).
Power BI Service Configuration
Roles should be assigned to Power BI users (or accounts in the other words), and this part should be done in Power BI Service. Save and publish the report into Power BI. I named this report as RLS PBI Desktop. You can name it whatever you want. After publish click on Security for the data set.
Here you can see roles and assign them to Power BI accounts in your organization.
You can each user to more than one role, and the user then will have consolidated view of both roles. For example a user with both roles for USA and Europe sales manager will see data from All Europe and USA.
Test Roles in Power BI Service
You can also test each role here, just click on ellipsis button beside each role, and click on Test as Role.
This will show you the report in view mode for that role. As you see the blue bar shows that the report showed as the role of Europe Sales Manager. You can change it there if you like.
With setting users for each role, now your role level security is ready to work. If the user login with their account, they will only see data for their roles.
Re Publish Won’t Hurt
As I mentioned in the very first paragraph of this post, the great thing about this new feature is that RLS is part of Power BI model. and if you publish your Power BI model again and again with changes, you won’t lose configuration in the web. You also won’t lose users assigned to each role, as long as you keep role names unchanged.
Don’t forget that this feature is still in preview mode, and we will see more updates for this in the future.