Dashboards

Row Level Security

This page is a draft. It's something you will have to decipher until I find the time to complete it :(

Introduction

What is RLS ?

RLS, or Row-Level Security, is a method of restricting data access in a database or business intelligence environment based on the characteristics of the user. Essentially, it allows administrators to control who can see what data within the same table or report. This is particularly valuable in environments where you have sensitive or private data that should only be accessible by certain users.

How is it different from RBAC ?

RLS in Power BI allows you to control access to rows of data based on the user viewing the report. This means you can create filters within roles defined in Power BI Desktop, and when users log into Power BI, they will only see data relevant to these filters.

RBAC, on the other hand, is broader and controls access at higher levels of the Power BI environment, not just at the data level. RBAC helps manage who has access to workspaces, reports, dashboards, and what kind of operations they can perform (like read, write, share, or delete).

How to enable RLS.

What you will need ?

The 'users table' must be implemented following your own business logic. In this example, we assume that user identification is done through the email address people use to connect to Power BI. If you are using an alternative way to authenticate people, you will have to adapt this example.

The 'user table'

The users table is a table that maps all the users that will have access to the data, to the data they can see. It should be structured in the following way :

  • user_id : the unique identifier of the user, which can be mapped back to an item of the Power BI authentication payload.
  • scope : the scope of the data available to the user. This is a string that will be used to filter the reporting data.

How to configure the RLS ?

The RLS is implemented by adding the user_table to your Power BI dashboard and by (inner) joining it onto the reporting table. The user_table can then be used to filter down data from the reporting table the user has been granted access to. To do so, you will need to create a new role in your Power BI dashboard, and add a filter to the reporting table that will filter down the data based on the scope of the user.

  1. Ensure that your data model has the necessary columns to define RLS filters. Basically, you need a set of dimensions you want to apply RLS to, to be available in both the reporting table and the user_table. The user_table should also include a column acting as a primary mean of identification for the user (in the example, we will be using the email column).
  2. Import the user_table into your Power BI dashboard, and create a relationship between the user_table and the reporting tables. The join must be done on the scope column.
  3. Create and manage a role:
    1. In Power BI Desktop, click on the Model tab, then click on Manage Roles.
    2. Click on Create to create a new role.
    3. In the Manage roles dialog box, enter a name for the role, and then click on Add Table Filter.
    4. In the Table Filter DAX Expression dialog box, enter a DAX expression that filters the data based on the user's email address. This is done to filter the users table to the current dashboard viewer. For example, if you want to filter the data based on the user's email address, you can use the following DAX expression:
     'user_table'[email] = USERPRINCIPALNAME()
    1. Click on OK to save the DAX expression.
    2. Click on Save to save the role.
    3. Click on Close to close the Manage roles dialog box.
    4. Publish the report to the Power BI service.

Example : implementing RLS at the school level

In this example, we will implement RLS to restrict access to the data based on the user's email address, so that each user can only see the data for the school they are associated with.

Step 1 : Create the users table

The users table should have the following columns:

  • email : the email address of the user
  • school : the school the user is associated with

Step 2 : Implementing RLS in Power BI

  1. Import the users table into your Power BI dashboard, and create a relationship between the users table and the reporting tables. The join must be done on the school column. This is the big deal: if your reporting table DOES NOT contain a school column, you won't be able to join the user_table table, and you won't be able to apply RLS to this table.
  2. Create a role :
    1. In Power BI Desktop, click on the Model tab, then click on Manage Roles.
    2. Click on Create to create a new role.
    3. In the Manage roles dialog box, enter a name for the role, and then click on Add Table Filter.
    4. In the Table Filter DAX Expression dialog box, enter a DAX expression that filters the data based on the user's email address. This is done to filter the users table to the current dashboard viewer. For example, if you want to filter the data based on the user's email address, you can use the following DAX expression:
     user_table['email'] = USERPRINCIPALNAME()
    1. Click on OK to save the DAX expression.
    2. Click on Save to save the role.
    3. Click on Close to close the Manage roles dialog box.
    4. Publish the report to the Power BI service.

Why is it working ?

Here is what happens when a user is viewing the report, deep inside the Power BI engine:

  1. The user logs with his email into Power BI and the report is displayed.
  2. Power BI sets USERPRINCIPALNAME() to the user's email.
  3. The DAX expression in the role is evaluated, and the user_table is filtered to the user's email.
  4. The tables joined on the user_table are now filtered to the user's email, and the user can only see the data for the school they are associated with.

A note about USERPRINCIPALNAME()

USERPRINCIPALNAME() is a DAX function that returns the currently authenticated user's email address. This function is only available in Power BI service, and not in Power BI Desktop. When you publish the report to the Power BI service, the USERPRINCIPALNAME() function will return the email address of the user viewing the report. This is useful for filtering the data based on the user's email address.

My customer wants RLS to work at school level, but also at class level. How can I do that?

Implementing RLS on multiple columns is actually easy to do : the trick is to rewrite the multiple-columns join into a one column join (yup). "If we don't have any issue at first, we don't have to look up for a solution" - Confucius (maybe, I have honestly no idea, but I'm sure he would agree, cause' you know, seems to me like a pretty good idea).

Since Power BI doesn't handle multiple-column joins (it's only been half a century since we walked on the moon after all), you will have to concatenate the columns you want to join on into a single column. This is done by creating a new column in all your reporting and user_table tables. Creating one column for multiple columns can be done by simply concatenating the columns you want to join on. Of course, the concatenation order must be exactly the same in both your user_table and your reporting table (otherwise the join won't work). If you want to show off how smart you are, you can do it in SQL BEFORE importing your data and use the hash of the concatenation as the join column instead of the concatenation itself (to save some tiny bits of precious memory).

PEVR

Introduction

Copyright © 2026