Power BI Tips: Using Row Level Security

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

[Country]=”USA”
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.

By | 2016-11-08T14:01:35+00:00 June 2nd, 2016|Power BI, Power BI Tips|5 Comments

About the Author:

Johann is the founder of Data Bear and specializes in getting the right data into the right hands, in a format that is quick and easy to understand. Revolutionizing your reporting experience from a time consuming frustration into dynamic interactive visualizations is what he takes pleasure in.

5 Comments

  1. Jack June 9, 2016 at 9:28 pm - Reply

    Hi there! Someone in my Myspace group shared this site with us
    so I came to give it a look. I’m definitely enjoying the information.
    I’m bookmarking and will be tweeting this to my followers!
    Wonderful blog and amazing style and design.

  2. Johann June 15, 2016 at 2:11 pm - Reply

    Hi Jack, thanks for the comments. Glad you like the blog 😉

    Wasn’t aware MySpace is still active…

  3. Anonymous November 9, 2016 at 6:31 am - Reply

    Hello Johann,

    Is there a way to apply sort by order on the these row level security that we create here? not on dashboards or reports while we create roles at particular time ?

    Any suggestions or advise is appreciated.

    Thanks,
    Firdous

    • Johann November 10, 2016 at 5:20 pm - Reply

      Hi Firdous,

      I not sure what exactly you are asking here. Please elaborate on this.

  4. corburt erilio January 21, 2017 at 2:26 pm - Reply

    Definitely, what a splendid website and enlightening posts, I surely will bookmark your site.Best Regards!

Leave A Comment