Secure and Share Part III: Understanding Row level security (Static and Dynamic) in Power BI:

Row level security provides accessibility to few rows which satisfy certain conditions.

This is also a part of security regulations in an organization.

Image URL: https://unsplash.com/photos/brown-padlock-on-black-computer-keyboard-FGH69mi53Mw

Let's see how we can implement row level security in Power BI.

I am using below dataset for demonstrating Row level security.

STATIC ROW LEVEL SECURITY:

Here is a small report for demo purpose in Power BI desktop.

As you can see, there are different countries displayed by number of quantity sold. We will create Roles according to the countries.

Here we have created three role. We can verify about the roles by clicking on 'View roles' tab in power BI desktop.

I am selecting Spain Manager here.

As you can see below, only Spain data is being displayed.

On Power BI service, the whole report is displayed for admin user, but for client, whatever the role has been defined in Power BI desktop, will be implemented.

This is about Static Row level security.

DYNAMIC ROW LEVEL SECURITY:

For displaying Dynamic Row level security, I have deleted the roles defined in Power BI desktop. To delete roles, go to the Modeling tab, click on 'Manage Roles' and delete roles.

I have imported another table in Power BI desktop with the managers' email addresses. Create a measure with user names.

An important point worth mentioning here is when we put this measure in power BI desktop, it will show the Operating System name, but when the same measure is visualized on Service, it will display user id.

Now, we will enter USERPRINCIPALNAME function to filter the data according to the defined roles.

Whenever any manager will enter email id to look at the data, that email id value will be compared to the email id in the Power BI dataset. If it will match, Power BI will filter the complete data model and will show the data which belongs to that region/email id.

I have created the table below as an example, imported it in Power BI desktop and connected with the main table with Country column.

Now save the Desktop report and re-publish to see the roles.

Let's go to the security of dataset published to check out what roles we got.

As you will see, visuals won't display now as RLS is implemented.

Here, we can enter one of the email id here to view as one role in Power BI service. We can also 'Test as Role' like in Power BI Desktop.

As you can see, implementing Row level security, both static and dynamic, is a fairly easy process and it is a very powerful tool for any organization's Data Protection.

Thanks for reading!