On numerous occasions, I’ve been asked if it’s possible to create separate views of the same dashboard dependent on a user’s Power BI credentials? This is possible through Power BI’s Row-level security (RLS). With RLS we are applying security on the underlying data of the dashboard, so when we open the dashboard, Power BI will first check and apply the RLS on the data, and then only will it populate the visuals in the reports.
Disclaimer: This Blog was written in 2016, for an updated version of RLS, please visit our most recent blog: https://databear.com/row-level-security-with-powerbi/
Power BI Row Level Security.
Let’s take a look at a simple demo. In this scenario, we have a report and a dashboard that contains data from different countries, and when users open the Power BI dashboard they should only see data from the country applicable to them.
We will obtain our data from SQL Server via the Power BI Desktop import mode as shown below:
Note: DirectQuery is also supported for SQL Server. For simplicity, we are selecting import mode for this demo.
For my sample report, I am using NorthWind sales data.
After designing a sample report in Power BI Desktop, as seen below, the report shows data for all countries:
After setting up the Row-level security, a user should only see data for the USA when logging in:
Publish (upload) the Power BI report to Power BI service and apply the required security using a DAX expression. Once the report has been uploaded, we will have access to the dataset of our sample report, RLS_Report.pbix. From here, select the ‘Security’ option of this dataset.
Go to Datasets > Find your dataset (RLS_Report) > click the ellipsis ( … ) > select Security
After selecting ‘Security’, a new window will open, follow the steps to create a role and add members to it, then provide the security rules, as follows:
security > create a role
> select continue from the next window
> Provide a name for the new role created > select Members tab > add a member to this role by typing their name > click Add > Save
> now go to Rules tab > select the table > then provide the DAX filter expression > Save
We are using a simple DAX expression:
For the role “USA_Users” all members in this role will only have rows that contain “USA” in the country column.
Testing the applied security:
We can test the Row-level security by clicking on the ellipsis (…) next to the role name and click ‘Test data as role’. From there we can verify that the rules are correct. As seen below, we have a report displaying data for the USA only.
When users in the group signed in, they will only see the data as depicted above. A very useful feature in Power BI. This concludes our Power BI tip, using Power BI RLS.
To learn more about Power BI, sign up for our training program.