Row-Level Security in Power BI

#PowerBI #Microsoft #PowerBIDesktop #RowlevelFiltering #RowlevelSecurity #Security #PowerBIService

Row-level security (RLS) with Power BI can be used to restrict data access for given users at data row level. For example, Project Manager of United States should only view data for the United states. Project Manager of Germany will not be able to see projects of India or United States. And someone from the executive team can see everything. We can define this security in the Power BI desktop itself and publish it with the Data model.

For this illustration I am using my own data as excel data source. I have project's information using which I have created a sample report.This report has Project location information where it is being executed, the bar chart represents the cost of each project

Now will go and create the role. We should build a role for Project Manager of United States. The Project Manager should only view their country's project. Go to Modeling ribbon under Security section there are 2 options Manage Roles and View as, for now will use Manage Roles!

This will open the Manage Roles dialogue box which looks very simple without any extra activities. It has 3 sections Roles, Tables and Tables filter DAX expression.

Click on Create and name the 'Role'. Once you create a Role in the Tables section you will find the list of Tables that are available. As of now I have only one Table.

Now select the Table of your country's information and click on More (3 dots/ellipse) it will give you options to Add filter , from the Add filter choose the Country field in my case it is Location.

Once you have selected the field, that particular field will show up in the Table filter DAX expression, as DAX expression automatically created [Location] =” Value”, update that value to “United States"

I am creating another Role for Germany, as you can see now I have 2 roles in the Manage Role dialogue box.

Now we will test the 02 Roles. To test the Roles, click on View as

This will open View as Roles dialog box

And Select United States role and click OK and observe the report it will only give the Project information that are in United states.

And on top of the report there is a message saying viewing as Country United State. If you click on Stop viewing you will see the normal report.

I named this report as Rowlevel and published into Power BI Service. Now click on Security for the data set.

Once you click on security it will open up Row-Level Security page. Here you can see roles and assign them to Power BI accounts in your organization.

I am adding a member into United States role (To add just type the name and click on Add and Save)

Now I will open the recently added User's Power BI and show the view. You can see that the Report is only showing the United States Project information.

This is it! The reason it is called Row level filtering is, the DAX filters are applied on the records on Row level. In the next article we will look at Dynamic filtering with Row-Level Security.

Please comment, share and follow