Dynamic vs Static Row-Level Security in Power BI: A Practical Guide

Imagine you're a national sales director. You've built an impressive Power BI report showcasing sales performance across all regions. Now, you need to share this single report with your regional managers. The challenge? The manager for Maharashtra should only see data for Maharashtra, the manager for Gujarat should only see Gujarat's data, and so on. How do you ensure each manager views only their relevant information without creating a separate report for every single region? This is precisely where dynamic row level security in Power BI becomes indispensable.

Row-Level Security (RLS) in Power BI is a powerful, specialized feature within the Power BI Service that allows you to control access to rows in a data model based on the user executing the query. It ensures that users can only view data that is relevant and authorized for them. This guide will walk you through both static and dynamic RLS, providing practical examples and the DAX code you need to implement them effectively.

The Business Problem: One Report, Many Audiences

The scenario described above is common for businesses operating across multiple geographies, departments, or client segments. Sharing a single, centralized report offers numerous benefits: consistency, easier maintenance, and a single source of truth. However, without proper security, it also poses a significant risk of exposing sensitive or irrelevant data to unauthorized users. For instance, a dedicated manager for the India region should only see data pertaining to India, while a manager for the China region should only see China's data. RLS allows you to restrict user access to data specific to their particular region or role.

Method 1: Static RLS (The Simple, But Tedious Way)

Static RLS is the more straightforward approach, but it quickly becomes unmanageable as your user base or data complexity grows. In this method, you manually create a distinct role for each group of users or each specific data segment. For example, if you have sales managers for different regions, you would create a separate role for each region.

How to Implement Static RLS

1. Open Power BI Desktop: Go to the 'Modeling' tab.

2. Click 'Manage Roles': This opens a dialog box where you can define new roles.

3. Create a New Role: Click 'Create' and name your role, for example, India_Sales_Manager.

4. Apply a DAX Filter: In the 'Tables' pane, select the table you want to filter (e.g., 'SalesData'). In the 'Table filter DAX expression' box, enter a simple DAX expression:

   [Region] = "India"

   This expression ensures that any user assigned to the India_Sales_Manager role will only see rows where the 'Region' column is "India".

5. Repeat for Each Role: You would then repeat this process, creating roles like China_Sales_Manager with the filter [Region] = "China", and so on for every region or segment.

Drawbacks of Static RLS

While easy to set up for a few roles, the main drawback of static RLS is its lack of scalability. Imagine having hundreds of regional managers, each needing access to their specific territory, or even individual sales representatives needing to see only their own sales. Creating and maintaining hundreds of separate roles and manually assigning users to each role in the Power BI Service would be incredibly time-consuming and prone to errors. This approach is not practical when dealing with a large and frequently changing user base.

Method 2: Dynamic RLS (The Scalable, Automated Way)

Dynamic RLS is the preferred method for most real-world scenarios because it offers a scalable and automated way to manage data access. Instead of creating numerous roles, you create a single, generic role that dynamically filters data based on the user's login ID in the Power BI Service. This means you don't need to define individual roles for every possible data restriction; the system automatically understands and provides the user with restricted information.

Key DAX Functions for Dynamic RLS

The magic of dynamic RLS lies in two crucial DAX functions:

• USERPRINCIPALNAME(): This function returns the user's User Principal Name (UPN) or email address with which they signed into the Power BI Service. This is typically the most reliable identifier.

• USERNAME(): This function returns the domain\username for users within your organization or the UPN for users outside your organization. While it can work, USERPRINCIPALNAME() is generally recommended for its consistency.

Step-by-Step Example: Implementing Dynamic Row Level Security in Power BI

Let's use an analogy: imagine an HR portal where employees can only see their own details. In Power BI, we can achieve this by filtering data based on the logged-in user's email address.

Scenario: You have an 'Employees' table with an 'EmployeeEmail' column, and you want each employee to only see their own row when they access the report.

1. Open Power BI Desktop: Go to the 'Modeling' tab.

2. Click 'Manage Roles': Click 'Create' to add a new role.

3. Create a Single Role: Name this role something generic, like EmployeeAccess or DynamicRLS. This will be your only role for dynamic filtering.

4. Apply the Dynamic DAX Filter: Select the 'Employees' table. In the 'Table filter DAX expression' box, enter the following DAX:

   [EmployeeEmail] = USERPRINCIPALNAME()

   This expression compares the 'EmployeeEmail' column in your data table with the email address of the user currently logged into the Power BI Service. Only rows where these two values match will be displayed.

   Here’s what the 'Manage roles' interface would conceptually look like:

   (Imagine a screenshot here showing the 'Manage roles' dialog box. On the left, under 'Roles', 'EmployeeAccess' is selected. On the right, under 'Tables', 'Employees' is selected, and in the 'Table filter DAX expression' box, [EmployeeEmail] = USERPRINCIPALNAME() is entered.)

5. Save the Role: Click 'Save'.

Once published to the Power BI Service, the service considers the ID with which a user signs in. Ultimately, each user will get access only to the data that is restricted for them, based on their login credentials matching a value in your data model.

Setting Up Your Data Model for Dynamic RLS

For dynamic RLS to work, your data model needs a crucial component: a mapping table. This table connects the user (typically by their email or UPN) to the specific data attributes they are authorized to see (e.g., region, department, employee ID).

Here’s what you need:

• A User Mapping Table: This table should contain at least two columns:

  • User Identifier: A column that stores the user's login ID (email address or UPN) exactly as it appears when they sign into Power BI Service. Let's call this UserEmail.
  • Data Attribute: A column that links the user to the data they can see (e.g., Region, Department, EmployeeID).

• Relationships: Establish a relationship between your user mapping table and the fact or dimension tables that contain the data you want to filter. For example, if your user mapping table links UserEmail to Region, you'd need a relationship between the Region column in your mapping table and the Region column in your 'SalesData' table.

Example User Mapping Table:

UserEmail	Region
manager.india@example.com	India
manager.china@example.com	China
salesrep1@example.com	India

With this setup, your dynamic RLS DAX filter would look something like this on your 'SalesData' table:

[Region] IN SELECTCOLUMNS(
    FILTER('UserMappingTable', 'UserMappingTable'[UserEmail] = USERPRINCIPALNAME()),
    "Region", 'UserMappingTable'[Region]
)

This DAX expression dynamically fetches all regions associated with the logged-in user from the UserMappingTable and then filters the 'SalesData' table to show only those regions. This advanced data modeling and DAX application is a core skill for any BI analyst, often covered in Juno's Data Analysis in Power BI with Copilot course.

For those interested in expanding their data analysis toolkit, exploring other related skills, such as those covered in free AI tools courses, can further enhance efficiency and insight generation in Power BI projects.

Testing Your Roles Before Publishing

Before you publish your report to the Power BI Service, it's crucial to test your RLS setup in Power BI Desktop to ensure it's working as expected. This prevents security breaches or incorrect data visibility.

How to Use 'View as' in Power BI Desktop

1. Go to the 'Modeling' tab: In Power BI Desktop.

2. Click 'View as': This option is next to 'Manage Roles'.

3. Select Roles: A 'View as roles' dialog box will appear. Here, you can:

   • Select a static role: Check the box next to a role you created (e.g., India_Sales_Manager) to see the report as if you were a user assigned to that role.
   • Test a dynamic role: Check the box next to your dynamic role (e.g., EmployeeAccess). Then, in the 'Other user' text box, type in an email address or UPN that exists in your user mapping table (e.g., manager.india@example.com or salesrep1@example.com).

4. Apply Filter: Click 'OK' or 'Apply'.

5. Verify Data: Observe the report visuals. They should now only display data relevant to the selected role or 'Other user'. A yellow bar at the top of your report canvas will indicate that you are currently viewing the report as a specific role.

6. Stop Viewing: To return to the full data view, go back to 'View as' and click 'Stop Viewing'.

Thorough testing is vital to confirm that your Power BI row level security examples are correctly implemented and that users will only see their authorized data in the Power BI Service.