Implementing Row-Level Security (RLS) in Power BI
%20in%20Power%20BI.png "Implementing Row-Level Security (RLS) in Power BI")
%20in%20Power%20BI.png "Implementing Row-Level Security (RLS) in Power BI")
Introduction:
Row-Level Security (RLS) in Power BI allows you to restrict data access for specific users based on defined roles. In this blog, we will demonstrate how to create and implement an RLS role named "country_filter" to restrict sales data visibility based on user countries. This is achieved using the DAX function: [Username] = USERPRINCIPALNAME()
Step 1: Setting Up Your Data Model
Before implementing RLS, ensure your Power BI model has the following:
Sales Table - Contains sales data with a Country column.
User Table - Contains Username and Country.
Relationship - Establish a relationship between the Country column in both tables (many-to-one relationship).
Step 2: Create a Security Role
1. Navigate to the "Model" View:
In Power BI Desktop, go to the "Model" view to work on roles and relationships.
2. Access the Security Settings:
On the "Modeling" tab, select Manage Roles.
3. Create a New Role:
Click Create and name the role as country_filter. Select the User Table from the list of tables.
4. Define the DAX Expression:
In the "Table Filter DAX Expression" box, write the following expression:
[Username] = USERPRINCIPALNAME()
5. Save the Role:
Click Save to finalize the role.
Step 3: Test the Role in Power BI Desktop
1. Simulate User Access:
On the "Modeling" tab, select View As Roles.2. Choose the country_filter role.
Enter a username (email) to test if the filtering works correctly.3. Validate Results:
Check if only the sales data for the corresponding country is displayed.
In our project, we have created five email ids as username and 5 different nationalities, i am using [email protected] to filter the data by the country United States of America as shown below,
When we Test the Role in Power BI Desktop, only the sales data for the corresponding country aginst the username is displayed as below,
Step 4: Publish to Power BI Service
1. Publish the Report:
Save and publish your Power BI file to the Power BI Service.
2. Assign Users to the Role:
Go to your dataset in Power BI Service.
Click on the ellipsis (...) > Security.
Select the country_filter role and add email addresses of users who should have this access.
Step 5: Execute and Test RLS in Power BI Service
1. Verify Access:
Log in as one of the assigned users.
Open the report in Power BI Service and confirm that only the relevant country’s data is visible.
2. Iterate and Fine-Tune:
Make necessary adjustments to the data model or DAX expression based on feedback.
Conclusion
Published on:
Learn more