I’ve been asked a few times recently, if it’s possible to create separate views of same dashboard depending upon the users Power BI credentials? Using Power BI Row Level Security (RLS), this is possible. 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 it will populate the visuals in the reports accordingly.

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 a user accesses the Power BI dashboard he/she should only see the data from the country applicable to them.

First step: 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.

RSL (2)

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.

RSL (3)

After setting up the RSL, when user A logs in he/she should only see data for and USA, as seen below.

RSL (4)

Second Step: publish/upload the Power BI file to the Power BI service and apply the required security using simple a DAX expression. Once the report has been uploaded/published, we will have access to the dataset of our sample report, RLS_Report.pbix. From here, select the ‘Security’ option of this dataset.

Steps: go to Datasets > Find your dataset (RLS_Report) > click the eclipses ( … )  > select Security

RSL (5)

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:

Steps: security > create a role

RSL (6)

> select continue from the next window

RSL (7)

> Provide name for the new role created > select Members tab > add member to this role by typing their name > click Add > Save

RSL (9)

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 which contains USA in the column country.

RSL (10)

Third Step: Testing the applied security: We can test the RSL by clicking on the ellipses (…) next to the role name and click ‘Test data as role’. From there we can verify if the rules are correct, as seen below, we have a report displaying data for USA only.

RSL (1)

When a user in the group signs 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.