Row-Level security (RLS) is a feature of Power BI that can be used to restrict data access for given users, so the appropriate person can see the appropriate records.
Row-Level security uses a DAX filters as the core logic mechanism, filters restrict data access at the row-level, and you can define filters within roles.
Following are some points that you need to know when using Row-Level Security:
- You can not assign users to a role within Power BI Desktop. You assign them in the Power BI service
- By default, row-level security filtering uses single-directional filters, whether the relationships are set to single direction or bi-directional.
- RLS supports only Import and Direct Query connections.
- You can define RLS only on the datasets created with Power BI Desktop. If you want to enable RLS for datasets created with Excel, you must convert your files into Power BI Desktop (PBIX) files first.
- When a report user is assigned to multiple roles, RLS filters become additive. It means report users can see table rows that represent the union of those filters
Lets create a simple report that displays the Total sales by month, year across the country and segment.
You can create either static Row-Level Security or dynamic Row-Level Security:
Static Row-Level Security, uses a fixed DAX value in the DAX filter, while Dynamic Row-level security uses the dynamic DAX values such as it uses a function userprincipalname() provides the login (email) of currently logged user in powerbi.com.
Implementing a Row-Level Security (RLS)
Here we will be looking at how to implement a static Row-Level Security(RLS), that uses a fixed value in the DAX filter.
Lets define roles and rules within Power BI Desktop, after that we will publish report to Power BI service and there roles are assigned to users.
Note that, you can not assign a role to users in Power BI desktop, Roles are always assigned to users in Power BI service.
Go to Modeling tab, then select Manage roles.
Once you click on Manage roles, you will see a Manage roles dialog box opens there you can create a roles, lets click on Create button.
Now we will create a role for user who want to see the data of country USA, so the uses who all will be assigned to this role, can see only country USA only.
Lets provide a role name as USA in roles section, and in tables section you will see a list of available tables in your data model, here we have only one table that is financials table.
Lets click on ellipsis (…), then in context menu navigate to Add filter.. and select Country column.
After that you will see a table filter DAX expression has been added with Country.
Now replace the “value” in the DAX expression with value from the Country.
Lets add the value as “United States of America” and such that this role will filter data only for those records for United States of America as the Country.
Once you create a DAX expression, you can also the checkmark above the expression box to validate the expression, if you are not sure.
After that click on Save button.
Validate the roles within Power BI Desktop
Lets validate the roles that you have created in Power BI desktop, for this go to View roles in Modeling tab.
Once you click on View roles, you will see a View as roles dialog box opens as shown below.
Lets select the roles USA from drop down and click on OK button, as you want to validate the role USA that you have recently created.
Once you click on OK, you will see data in report has been filtered only for country USA, that means user who has assigned this role can see only country USA data.
Also you can see, at the top of report it indicates that “Now viewing as : USA”, that means you are viewing report as USA role.
Now to remove the role and see all the records on report, just click on Stop viewing as shown below.
Now you can see, all the records on report.
Validating Row-Level Security in Power BI Service
Now time to publish the report to Power BI service, and validate the RLS also assign roles to a users.
Lets save the report with name Report_RLS and inside the Home tab, click on Publish button.
After that select a workspace, where do you want to publish the report, once it is published. Open the report in Power BI service.
Now, select the dataset of report Report_RLS in workspace, and open the menu and select Security.
Once you select security, a Row-Level Security screen opens, there you can see roles USA that you created in the Power BI desktop are present.
Assign users to a roles
Lets add users to a roles or you can say members also, for this select Role USA, that is selected by default as you have only one role and the email address for the user who can see the data for country USA only.
Lets add a user to a role by typing in the email address or name of the user or security group.
After that click on Add button.
Once you add, the user to a roles. You can see the added users name also you can also see how many users are the part of the role by the number in parentheses next to the role name, or next to Members.
After that click on Save button.
After assigning a roles to a users, you can see now one user is the part of role USA.
Lets validate the roles, the quick way of validating the roles is just click on ellipsis (…) and select Test as role.
And you will see that a report opens which display country USA data only.
So in this section we have seen how to create a static Row-Level security (RLS) and validate it in Power BI desktop, also how to validate Row-Level Security and add members to a roles in Power BI service.
Also Reads..
Publish Report to Power BI Service
Introduction to Workspace in Power BI, Assign Roles and Permission
Monitor usage and performance in workspace
