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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.