Another aspect of sharing is the security of the dataset. Enabling different roles and giving users access to different levels of data is called Row Level Security. This post explains the details of this security method and how to configure it in Power BI Desktop. Row Level Security enables you to apply security to roles and adds users to each role. An example is helpful when you want people from one branch, city, department, or store to be able to only see their part of the data and not the whole data set. Power BI applies that through a row-level security configuration on the Power BI model itself. So regardless of what source you are importing your data from, you can apply row-level security on it. If you like to learn more about Power BI, read Power BI online book; from Rookie to Rock Star.
What’s Row Level Security in Power BI Desktop?
There are multiple levels in which you can secure the data in a Power BI Dataset. Row Level Security is about applying security on a data row level. For example, sales managers in the united states should only see data for the United States, not Europe. The sales Manager in Europe won’t be able to see sales in Australia or the United States. And someone from the board of directors can see everything. The reason was that Row Level Security wasn’t part of the 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 AdventureWorksDW Excel sample data source. You can download it from here. There is a DimSalesTerritory in this dataset, with country details like below. This is the table which we will be using for applying security filters.
Create Sample Report
Let’s start with creating a sample Power BI desktop report from the AdventureWorks Excel file. I only select DimSalesTerritory, and FactResellerSales for this example;
without any changes in the 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 country, which can be used for creating row-level security on Geo-location information easily. Now let’s add one card visualization for the total Sales Amount. The below screenshot is the layout of this sample report now;
In this view, our total Reseller sales amount is $80M, and we have sales values for Australia, Canada, France, Germany, the UK, and the USA. Now let’s create roles.
Now let’s create roles for that. Our goal is to build roles for sales managers in Australia and Canada. They should each only see their group or country in the data set. For creating roles go to the Modeling tab in Power BI Desktop. You will see a section named Security there;
Click on Manage Roles to create a new role. You will see the Manage Roles window, which has three panes below.
You can create or delete roles in numbered one pane, You can see tables in your model in numbered two 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 three pane, or you can use the user-interface profited to define the filters.
Now Create a Role, and name it “Australia Sales Manager” You will see two tables in the Tables section: FactResellerSales, and DimSalesTerritory. With a click on Add, you can add the rule you want for SalesTerritoryCountry Column.
You can also switch to DAX editor and see the DAX expression.
Now create another role, name it Canada Sales Manager, put a filter on SelesTerritoryGroup this time, and change Value to “Canada” as below;
Testing Roles in Desktop
Great, we have created our two sample roles. Now let’s test them here. 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 it. We can even combine multiple roles with seeing a consolidated view of a person who has multiple roles. Go to the Modeling tab, and choose View As Role option.
Choose Australia and Canada Sales Manager, and click on OK. You will see sales for Australia and Canada only showing with a total of $15.97M and only countries Australia and Canada.
You can also see at the top of the report there is an information line highlighted showing that the view is Canada Sales Manager. If you click stop viewing, you will see the report as a normal view (total view).
Assigning Users to Roles in the Power BI Service
Roles should be assigned to Power BI users (or accounts, in other words), and this part should be done in Power BI Service. Save and publish the report into Power BI. I named this report 00 Static. You can name it whatever you want. After publishing the report, click on Security for the data set.
Here you can see roles and assign them to Power BI accounts in your organization.
You can set each user to more than one role, and the user will then have a consolidated view of both roles. For example, a user with both roles for the Australia and Canada sales manager will see data from Australia and Canada.
Test Roles in Power BI Service
You can also test each role here. Just click on the ellipsis button beside each role, and click on Test as Role.
Test As Role will show you the report in view mode for that role. The blue bar shows that the report showed the role of Europe Sales Manager. You can change it there if you like.
Setting users for each role makes your row-level security 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 chapter, the great thing about this new feature is that RLS is part of the Power BI model. And if you publish your Power BI model repeatedly with changes, you won’t lose configuration on the web. You also won’t lose users assigned to each role if you keep role names unchanged.
Row Level Security gives users different views of the data from the same Power BI content. As you have learned in this chapter, implementing row-level security is simple. This method is called ROW level security because of the DAX filter applied on the data row level.
In this article, you’ve learned about a specific type of row-level security called Static row level security. It is called static because the filter values are statically determined in DAX expressions. Maintenance costs are very high if you want to apply such a filter for thousands of roles. In an ideal world, you want to apply security based on users’ login automatically. In the next article, you will learn about Dynamic Row Level Security which is the next step in applying security in more complex scenarios.