Hello friends, today, I am going to discuss a very informative and useful topic of Power BI Row Level Security. First, we need to understand the basics of Row Level Security by understanding its functioning. Row Level Security is used to hide the data for the given users which means if I have created a report for IT company, an IT company has so many departments like HR, Sales, Marketing, Development. So, the Sales department will only see the Sales report not any other reports and this is applied to other departments too.
There is one thing to note that, in Power BI service, members you add in your workspace have access to see your datasets. Even, RLS does not restrict its access. To apply the RLS, you first need to assign roles in Power BI desktop which will be our first step.
How to define roles and rules in Power BI desktop?
The roles and rules are defined within our Power BI desktop. To understand it in a better way, let’s take an example. I have a report of Human Resources Sample which I am going to use here. You can use any report. The report looks like this.
When you publish the reports to Power BI service, the roles definitions also get publish with the reports. If you check the report a little closer, you will see a graph Bad Hires by gender. So, our task is to create a role for male that is male persons can only show male reports.
Let’s do it step by step.
1. After creating a report GO to modeling tab.
2. Go to Security option and click on Manage Roles as shown below. A pop up will open for managing the roles.
3. The screen is now divided into three sections i.e Roles, Tables and Table Filter DAX expression. First, you need to give the role name clicking on Create. I am giving its name Male.
4. Second is to choose the table on which you want to apply the Role. You need to add a filter on Gender Table as a DAX expression in the Table filter DAX expression. The DAX function for this task is going to be [Gender] = “Male”
5. Click on Save. Your role has been created.
6. To test your role whether it is working properly, GO to View as Roles. You will see the role we created above is now present in roles. Check on Male and the report is now going to change and it shows visualizations related to Male only as shown below.
Now to assign these roles to Power BI users, you need to publish this report to Power BI desktop. Our next is going to be the same is to publish the report to Power BI service. After publishing, we need to open our Power BI service account and our next step is going to be performed here.
Assign Roles to Users in Power BI service
To assign roles to the users, we need to go to the dataset where we publish the report. In my case, I have uploaded the report in my workspace. The steps are as follows.
1. Go To Datasets. You need to find your dataset here.
2. Click on Ellipse (…) and then Security which will redirect you to the Row Level Security Page.
3. You will find here the role you created in the Power BI desktop. Now, you need to assign this to different users. You can also Test as Role by clicking on ellipses and it will redirect you to report filtered by male only.
4. Now you need to add members related to this role. I am going to assign this role to my friend email@example.com. You need to enter the mail id of the user to whom you want to assign this report and click Add.
5. Click Save. Hurray, our task is completed!
6. You will see one member is added to this role. You can add as many members as you want to this role and also you can create multiple roles too in your Power BI desktop.
7. To check whether the role is working properly or not, I am going to share this report with my friend Prashant and I have my friend credentials too.
8. To share this report, Go to your report and click on Share as shown below.
9. It will open a sidebar popup where you need to fill some details. Enter the email id and an optional message if you want and click on share.
10. Now, I am going to open my friend’s account. I am opening shared reports now.
11. Opening the Human Resources Sample and see the report. The report is only showing the male data Prashant. We have successfully achieved our task.
In this article, we have been through creating roles in power BI desktop to assign the roles in Power BI service and also verified the complete process by sharing the report to the user and checked the report in user account. I tried to cover the complete process of RLS and I hope you find this blog informative. If you have any query, you can post your questions in the comment section below. Thanks!