Implementation of Power BI Row Level Security (RLS)

Implementation of Power BI Row Level Security (RLS) when connecting to On-Premises databases from the cloud.

Row-level security enables controlling access to rows in a database table based on a user role. With new updates from PowerBI team we can now implement a logic when users from different roles/groups are limited to see only the data allowed to read by their role/group.

Previously, row-level security could be implemented in Power BI Service by leveraging Analysis Services On-Premises, based on user with USERNAME function of DAX. Now Microsoft added the RLS support to cloud models.

Let’s look at how to implement Row-level security on PBI dataset level and how to directly connect to Oracle on premises from the Power BI service in the cloud.

For demo purposes we created the following three users and two groups in Office 365 AD:

  • Bill Smith – global sales manager(USA) can see orders all of customers.

“PBI Demo 11” group

  • Justin Trudeau – regional manager(Canada) can see orders customers from Canada only.

“PBI Demo 14” group

  • Klaus Schmidt – regional manager(Germany) can see orders customers from Germany only.

“PBI Demo 14” group

Figure 1: Users Location

 As data source we choose the well-known “Northwind” database.

Figure 2: Tables relationship schema

Power BI Service is connected to the database through the Enterprise Getaway.

Figure 3: Data Flow schema

Next, we created a simple sales report in the Power BI desktop and publish it in “PBI Demo 11” group. Each Office 365 groups is shown as workspace in Power BI Service.

Figure 4: Publishing to Power BI

Let’s return back to our demo users and see how to configure RLS for them. User Bill Smith can view the report on Power BI website and he has a permission to apply row level security.

Figure 5: The Sales Report

We pined a live page to the new “Sales Geography” dashboard.

Figure 6: Pinning the Sales Report to a new dashboard

On Datasets tab we opened the dataset and chose security tab.

Figure 7: Security tab

In Row-Level Security section we added two new Roles: “Germany Managers”,” Canada Managers”.

We added Justin Trudeu user to the “Canada Manager“ role members.

Figure 8: Row-Level Security section

Now we create a new rule for  “Canada Manager” role. In rule expression we can use DAX expressions and any data tables from the dataset. We created very simple DAX expression for our demo: country field from Customer table is filtered by value of Country column.

Figure 9: Row-Level Security section

The same operation we made for Klaus Schmidt user from “Germany Manager” role.

In our demo use in the “Global sales manager” role can share the dashboard with regionally managers and view all orders for all customers.

Figure 10: The dashboard sharing

When Klaus Schmidt user logs in in PBI Service, he can view orders for customers from Germany only.

Figure 11: The regional manager dashboard

Figure 12: The global sales manager and regional manager dashboards


As Power BI evolves, we see more and more features helping users to provide more sophisticated reporting. Row level security feature, which was until recently only available through Microsoft Analysis Services, now available through native Power BI means, allowing connection to other databases, such as Oracle. Being released as a new feature, it is still based on familiar tools, such as DAX queries, while providing convenience of configuring RLS from within a web browser.

Analyzing of the Orders.

Analyzing of the Orders.

Happy owners of the account and the PowerBI Desktop Application can enjoy of first days of new year and analyze how they spent money with Amazon for several past years.

The first step to creating this dashboard is to export your order history from

  1. Navigate to and click on “Your Account”
  2. Under the heading “Order History”, click on “Download Order Report”
  3. The next screen will allow you to select which data to export and the date range desired. Keep the report type set to Items to get the breakdown of categories in the dashboard above. Make your selection and click on “Request Report”. The report will be processed and will appear in your report list below. Download this .csv file to your local machine to use as the data source in the Power BI template in the next step.

Next, download the Power BI Template from here and continue with the following steps:

  1. Download  here  and open the attached AmazonOrdersTemplate.pbit file and open it in Power BI Desktop
  2. A popup box appears asking for the full path of the .csv file exported in the above steps. Provide the full path to the file on your computer and click the “Load” button in the bottom right. (TIP: Make sure the file is closed or you’ll hit an error here. If so, close the file and relaunch the Power BI template)

Analyzing of the Total Amount by Year chart I was surprised that I have started spend more and more money on Amazon. Good for Amazon.

Thank you Samuel Lester for idea to analyze of Orders and article

Hello world!

Hello world!

My name is Julia Pereversina. I am Software and BI Developer.

Microsoft-certified Business Intelligence Analyst and Developer with more than tree years of experience in building BI solutions. The key responsibilities include developing a full cycle of BI solution as well as designing and constructing data warehouses, extracting, transforming, cleansing, and loading raw data into data warehouse using ETL tools, managing data warehouse schema, assisting with SQL tuning, creating views, tables, etc. Additional expertise includes report design, development and maintenance. Last several years have been intensely working with Microsoft Reporting Services and Power BI. Technology expertise includes Azure Services, Azure, Dynamics CRM, SQL Server, Data warehouses, SQL, SSIS, SSRS, MS Excel, MS Visio, MS Project, MS Power BI, JavaScript, .NET, ASP.NET, PHP, HTML, CSS, and more.  It’s about me 🙂