How to Apply Row Level Security in Power BI
Row level security (RLS) in Power BI allows you to restrict data access to specific rows based on user roles. This feature is useful when you have sensitive or confidential data that should only be visible to certain individuals within your organization.
Setting up Row Level Security
To apply row level security in Power BI, follow these steps:
- Open your Power BI desktop application.
- Load your data into Power BI using any supported data source.
- Click on the “Modeling” tab in the ribbon.
- Click on the “Manage Roles” button in the “Tables” group.
- In the “Manage Roles” dialog box, click on the “Create” button to create a new role.
- Provide a name for the role and define a DAX expression that determines which rows should be visible for this role. For example, if you want to restrict access to a specific region, you can use the following DAX expression:
[Region] = "North". - Add users or groups to the role by clicking on the “Add users…” button and selecting the appropriate users or groups from your organization’s Active Directory.
- Repeat steps 5-7 for each additional role you want to create.
- Click on the “OK” button to apply the row level security settings.
Testing Row Level Security
To test the row level security settings, follow these steps:
- Save and publish your Power BI report to the Power BI service.
- Assign the appropriate roles to the users or groups you want to test.
- Sign in to the Power BI service with the credentials of a user assigned to a specific role.
- Open the report and verify that the data is restricted based on the row level security settings.
Example
Let’s say you have a sales dataset with the following columns: “Region”, “SalesAmount”, and “SalesPerson”. You want to restrict access to the data based on regions, so that each salesperson can only see data from their own region.
To achieve this, you would create a role in Power BI named “SalesPerson” and define the following DAX expression for the role:
[SalesPerson] = USERNAME() && [Region] = "North"
This expression ensures that each salesperson can only see data where the salesperson’s name matches the current user’s name and the region is “North”.
By following these steps and customizing the DAX expressions based on your specific requirements, you can successfully apply row level security in Power BI.