Dynamic Row Level Security in Power BI

Interested in learning how to set up row level security (RLS) in Power BI when you have varied criteria? In this post I’ll walk through a scenario of setting up dynamic row level security in which your users have unique access needs. In other words, one user has access to the Northeast and Midwest regions, another has access to the Southeast, and two others overlap with access to the South and Northwest regions.

In my demo (please check out my video for a detailed view and the code used) I have 4 tables, users, user regions, regions, and sales. These tables are connected in various ways such as the users table connected to a many to many or bridge table, users and regions connected, and the regions table connected to the sales table.

What I want to do is apply a filter or row level security to the users table, or to use information from the users table and pass that all the way down to the regions table. Because this is a many to many relationship, I can’t do this just by saying who is logged in.

One way to do this is to create bi-directional relationships but that introduces other problems, so it may not be accessible for your specific case. I’m going walk you through doing this using DAX. I’ve already built the measures, so what I’ll show you is how to build this DAX expression from the pieces that comprise it.

Step 1: Retrieve User ID from Users Table

• I do this by using the look up value measure. I’m going to return the user ID using the users email as the match term and the username expression to pull that in from Power BI.
• This measure now gives user IDs a number, so I am now user ID 1. Once this measure is done, we can copy it as we are going to use it again.

Step 2: Filter User Region Table Based on User ID

• Now that we have the user ID, we’ll go to that bridge table and we’re going to filter that based on the user ID from Step 1.
• What I’m doing here is coming to that user regions table and asking it to show me the information relevant for User ID 1, which will return a filtered result.
• To do this I’ll use the filter expression to filter the user regions table where the user region user ID is equal to the value that’s returned from the previous measure (so the look up value is equal to 1).

Step 3: Select Columns of Region ID from Table Returned in Step 2

• So, we’ve filtered this table, and now we want to get a column from it. To do this, we use the select columns expression.
• This table filter is basically the combination of the two DAX expressions we just used.
• Now with the select columns expression, we’re going to ask to use that table we filtered, but just get the column with the region IDs.
• This is the expression we’ll use to set our row level security.

Step 4: Set Up Role That Filters the Region Table Using All Region IDs Identified in Step 3

• Next step is to take that combination expression and put it into our roles.
• To do this we go up to the modeling tab and select manage roles.
• Select the role and the tables, in this case user role and regions table.
• Under table filter DAX expression, we want to add a filter under user ID. Instead of value, I’m going to paste in the measure that we used in step 3 or up to this point.
• To get this expression to work for our row level security, we’ll have to replace the equal sign at the beginning of our DAX expression with the IN operator. This IN operator is similar to SQL where this expression or measure is returning multiple results.
• But we want to tell it, filter the region IDs that are going to be returned in this set.
• So, I’ll set this role and now if I come in and view as that role, I should only see my regions. As I simulate that role, it will filter our sales table to show us those two regions.

This will work effectively across any organization structure, as long as the table is organized accordingly. It’s an easy way to get information from our users table, pass it over a many to many relationship and introduce it to our sales table by using that IN operator and the select columns and filter combination.

If you’d like to learn more about using row level security or about incorporating Power BI in your organization, our Power BI experts are here to help. Data and the cloud is what we’re all about and we are here to help with any Azure product or service.

Our experts can help you use your data to grow your business, whether you work on premise, in the cloud or a hybrid approach. Contact us to start a conversation or click the link below. Let us help you take your business from good to great.