Tallan Blog

Tallan’s Experts Share Their Knowledge on Technology, Trends and Solutions to Business Challenges

Row-Level Security in Power BI: Part 1 – Roles and Users

Securing Reports

Let’s say the Finance Department of a clothing retailer has some great reports that let them see all the sales across the United States; so great, in fact, that they want to share them with all Regional Managers so they can communicate about the hot spots in their region. The problem is the Regional Managers aren’t permitted to see data outside their region, and giving them access to these reports would allow them to filter to any region they wanted. We could create separate Datasets and reports filtered to the region for the manager that is given access to them, but that would be time-consuming, and a nightmare to maintain. Luckily, Power BI provides the ability to implement Row-Level Security (RLS).

So, what is RLS? Simply put, it controls a user’s access to each individual row of the Dataset. In Power BI this is accomplished filtering the data in DAX based on the user. In the example below, we can see Billy requesting a list of all the states; Power BI then matches Billy to his roles and filters the states down to the ones in his roles.


RLS Example

Billy opens a Report with States on it


In the report, we might have a page that shows sales data by location, and we want our Regional Manager to only see the sales data in his region.

Roles in Power BI

In Power BI, RLS is defined in Power BI Desktop through roles; roles define DAX filters for whichever tables they are intended to secure. This means that we need to be careful with the structure of our Data Model to ensure that we are filtering on the highest-level entity possible. In the example below, we see a Dataset similar to the scenario above and a report that it drives.


Role RLS - DataModel

Access to Geography is restricted by selected Regions, and these Geographies can, in turn, restrict the Sales


Unsecured - Regional Sales

Report with no Roles applied



Creating Roles

Now that we have the appropriate structure set up for the data to be able to filter by region, we will need to set up our roles. In Power BI Desktop this can be done by going to Modeling > Manage Roles, we can then create new roles in this view on the left and define their table filters to the right.


Manage Roles

Here we can add the two Roles from the example above


Now that we have a couple of Roles set up, we can use Power BI to test them out using the View as Roles button next to the Manage Roles button.


Roles - View as Roles

Here we can see the two Roles we added earlier and select those we would like to test


Roles - View as Mountain

Report as someone with the Mountain Role would see it (Note the bar at the top denoting the Roles that we are viewing the Report as)


Roles - View as Mountain & New England

Report as someone with the Mountain and New England Role would see it



Dynamic RLS

In addition to locking down access to tables with hard-coded filters, we can also leverage security that is built into the data. Let’s say that we are sourcing this data from an application that handles security in its database by granting user’s access to specific states. In this case, we can bring in this security information and use these relationships to filter data by who is viewing the report. Below, we can see these Security Tables brought into Power BI; the User Table (greyed out because we are hiding it in the final report) filters the State User Table to a combination of the user that is logged in and the state’s data they are permitted to see. This table further filters the State Table, which then filters the Geography Table.


Dynamic RLS - DataModel

Note the relationship between State User and State; State User will likely have many users that will be able to see the same state, so, this is a one-to-many relationship, and in this instance, the State Users are the many. Normally, in Power BI, it is the one side of a relationship that filters down the many sides (the same way that state filters geography) but, for our purposes we want the State User to be able to filter the states. To achieve this, we must make the relationship filter in both directions and apply the security filter in both directions.


Dynamic RLS - Cross Filter Relationship

Here we see our Guest User has access to CT, NY, and MA


Now that the Data Model is set up, we need to create a role that will be able to filter by the logged in user’s name. To do this, we can create a role that filters the user using the DAX function USERPRINCIPALNAME(); not to be confused with USERNAME() which may be the Domain\Login (Depending on your AD,) the USERPRINCIPALNAME() returns the user’s email address.


Dynamic RLS - Role

At this time, anyone with this role applied will have the User Table filtered by their email address and subsequently, will have the rest of the tables down the line. To test this, we will have to define who we are logging in as to see the report they will see.


Dynamic - View as Roles

We set our UPN in the Other user field (as you can see this is case-insensitive)


Roles - View as Dynamic

Now we only see the three States our Guest has access to



Assigning Roles

Now that we have all of our roles created we will need to assign them to the appropriate users.  We will need to publish a PBIX to a Workspace in the Power BI Service; then we go to Datasets [Our Dataset with RLS]  > Security. Here we can assign users to roles; we select the role we want to add to and start typing, Power BI will be able to filter by email or name within your organization. Note that anyone that is not an admin viewing a report that they have no roles assigned to, but that uses RLS will not be able to load any visuals.


Assign Roles - Dataset

Assign Roles - Assignment

Here we have given the PowerBI Guest user the Mountain Role


Guest User - App View Mountain Role

When PowerBI Guest views the App all that is shown is the Mountain Region data


These Security Roles will be applied to anyone viewing the report in a Published Power BI App, and changes to the security will not require the app to be published again. These roles will also affect anyone that is a member of the Workspace; provided that the Workspace is set up to allow members to only view content and not edit.


Workspace Configuration

Stay tuned for Row-Level Security in Power BI: Part 2 RLS in Embedded Reports; in which we will go over handling RLS for you reports embedded in your internal and customer-facing Applications.


Feel free to start a conversation below or visit our BI team’s page to learn more about how Tallan can help your organization get the most out of Power BI and your existing data infrastructure.

Share this post:

7 Comments. Leave new

Did you publish the second blog for implementing RLS in Power Bi embedded reports. What’s the link for that?

I have been looking into a few different approaches to RLS embedded to find the best one, you can expect to see a post by the end of the year. If you have a more pressing need, feel free to reach out to Tallan’s Data & Analytics

Best instructions to accomplish what I needed to do. Thank you!!!

Hi, This is very helpful. I have a question for you. How will you allow email ID to view regional level to those user who got access to view all the states within their own region?

Denoel Eller
June 4, 2019 10:23 pm

Let’s assume that although the finance department doesn’t want to allow regional managers to see specific data from other regions, they do want to share information like their share of total country sales or how it’s particular region performs against the average of other region sales or maybe their ranking among all country regions.
How can this type of info be available to a specific region ?
Fair to say that I am not an expert in power bi…

March 20, 2020 7:17 am

I would like know what if the user want to see all the groups that are available? It is not feasible to add row one by one for all the groups and it will be tedious also. So whats the simplest way to do that? So I would to achieve the followings.

1. If IN,US,UK and CA are the State that are available. What should I do to give access to all the groups.

2. If I have given access to IN,US and CA States to the user in securtity table, what should I do to give access to only those groups who have been given access in the security table. Not like giving access to all the groups like above.

Please help me out on this…


I have a security table containing columns GlobalID,level1,level2,level3,level4,level5,level6,level7,Profile
Based on the values in Level1 to level7, the data should get filtered for the GlobalID
The transaction table contains level 1 to level7 and measures.Currently there is join between security table and transaction table on KY column which is concatenation of fields : SourceSystemRegion, Level1 to Level7, Customer Name,GlobalCustomer
How do we implement row level security for this case


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>