Introduction
Row level security in power BI is a powerful features that allows you to control access to data at the row level based on user roles. Dynamic row level security takes this a step further, enabling you to define security rules that can be dynamic applied based on user attributes or conditions.
Step 1 — Prepare the Data Model
- Before setting up dynamic row level security, you need to have a well structured data model in power BI.
- Ensure that you have data table that includes the necessary user attributes or roles. For example, you might have a “Users” table with columns like “Username” , “Role” or any other relevant attributes that you want to use for dynamic row level security.
Step 2 — Enable Row Level Security
To enable row level security in power bi, follow these steps:
- Open your power bi desktop file that contains the data model.
- In the left navigation pane, click on the “Model View”.
- Under the security section of the ribbon, click on “Manage Roles”
- In the manage roles dialog box, click on “Create” button to define a new role.
Step 3 — Define Dynamic Row Level Security Rules
In this step, you will define the dynamic row level security rules based on user attributes or conditions.
- In the “Manage Roles” dialog box, give your new role a name, such as dynamic RLS Role.
- In the “Table filter expression” box, you will write the DAX expression that defines the dynamic row level security rule.
- This DAX expression should evaluate to a boolean (True/False) result. For example, if you have a “Users” table with a column “Role” and you want to allow users with the role “Manager” to see only their own data, the DAX expression might look like this:
= “Users” [Role] = “Manager”
- This ‘USERNAME()’ function retrieves the current users username and the DAX expression checks if the user’s role is “Manager” and if the user’s username matches the value in the “Username” column of the “Users” table.
- Click “Save” to save the role.
Step 4 — Test the Dynamic Row Level Security
After defining the dynamic row level security rules, its time to test them to ensure they work as expected.
- Go back to the “View” tab in power BI Desktop.
- Click on “View as” in the security section.
- Choose the role you created earlier, such as “Dynamic RLS Role”.
- Now, as you interact with your report or data visualizations, Power BI will apply the dynamic row level security rules based on the current users attributes or conditions. You should see that the data is filtered according to the rules you defined.
Step 5 — Publish the Report to Power BI Service
Once, you have verified that the dynamic row level security is working as expected in power BI desktop, its time to publish the report to power bi service.
- Save the power BI desktop file.
- Click on “Publish” in the top right corner of the power bi desktop window.
- Choose the workspace where you want to publish the report and click “Select”
- After the report is published, goto power BI service (https://app.powerbi.com) and open the report.
Step 6 — Test in Power BI Service
- In power BI service, test the dynamic row level security by using different user accounts with varying attributes or roles.
- Ensure that the data is appropriately filtered based on the dynamic RLS rules you defined.
Step 7 — Monitor and Maintain
- Once your report is live and being used by different users, monitor its performance and the application of dynamic row level security.
- If you encounter any issues or receives feedback you may need to review and modify the DAX expressions accordingly.
Conclusion
Implementing dynamic row level security in power BI allows you to control data access at a granular level based on user attributes or conditions. By following this step by step guide, you can secure your data effectively and provide users with a tailored experience based on their roles or attributes.
