Power BI Advanced — RLS Workshop: Implement Static and Dynamic Row-Level Security

Power BI Advanced Series · Power BI Embedding & Security · by Raushan Ranjan, MCT

Welcome to this hands-on workshop guide on Row-Level Security (RLS) in Power BI! Our objective is to implement both Static and Dynamic RLS using a practical dataset, test access as different users, and understand how to scale this for real-world scenarios, including embedded token logic. RLS is a critical feature for ensuring users only see the data they are authorized to view.

📦 Part 1: Load and Understand the Dataset

For this workshop, you'll need a simple Excel file named RLS_Demo_Data.xlsx with two sheets:

• SalesData: Contains columns like EmployeeID, Region, SalesAmount.
• Users: Contains columns like EmailID and Region, mapping users to their respective regions.

(If you don't have this file, you can quickly create one with sample data in Excel.)

Step 1: Import Data

• Open Power BI Desktop.
• Click Home → Get Data → Excel workbook.
• Select your downloaded file: RLS_Demo_Data.xlsx.
• In the Navigator window, check the boxes for both sheets: SalesData and Users.
• Click Load. The data will be imported into your Power BI model.

🔍 What We Are Doing:

We are loading two distinct tables:

• SalesData: This table represents your core business data, containing employee sales entries along with their corresponding regions.
• Users: This is a lookup table that contains user email IDs mapped to specific regions. This table will be crucial for our dynamic RLS implementation.

🧱 Part 2: Static RLS Implementation

Static RLS is straightforward: you define a role, apply a fixed filter to a table, and then manually assign users to that role in the Power BI Service.

Step 2: Apply Static Role Filter

• Go to the Model View (the icon that looks like three tables connected, on the left panel in Power BI Desktop).
• In the top ribbon, click Modeling → Manage Roles.
• In the "Manage roles" window, click Create.
• Name the new role: EastRegionRole.
• In the "Tables" section, find and select the SalesData table.
• In the DAX filter expression box on the right, add the following DAX expression:

  [Region] = "East"

• Click Save.

Step 3: Test the Static Role

• Still in Power BI Desktop, click Modeling → View As Roles.
• In the "View as roles" window, check the box next to EastRegionRole.
• Click OK.
• Observe your report visuals and the data view (if you switch to Data View).

✅ You should now see only sales data pertaining to the "East" region. All other data will be filtered out.

🔍 What We Are Doing:

Static RLS applies a fixed filter (in this case, `[Region] = "East"`) for everyone assigned to the `EastRegionRole`. This means anyone assigned to this role will *only* see data for the East region, regardless of their actual login. You will later assign specific users in the Power BI Service to this role.

🧠 Part 3: Dynamic RLS Implementation

Dynamic RLS is more flexible: it automatically filters data based on the user's login credentials, typically by mapping their email to a specific data attribute (like region).

Step 4: Build Relationship

For dynamic RLS to work by mapping users to regions, we need a relationship between our `SalesData` and `Users` tables.

• In Model View, drag the Region column from the Users table to the Region column in the SalesData table to create a relationship.
• Double-click the newly created relationship line to open its properties.
• Ensure the relationship is single-directional, flowing from Users to SalesData. This means the `Users` table will filter the `SalesData` table.
• Click OK.

Step 5: Create Dynamic Role

• Go back to Modeling → Manage Roles.
• Click Create New Role.
• Name the role: DynamicRLS.
• Select the Users table.
• In the DAX filter expression box, use this DAX expression:

  [EmailID] = USERPRINCIPALNAME()

  This expression means: "Filter the `Users` table to show only the row where the `EmailID` matches the email of the currently logged-in user." Because of the relationship, this filter will then propagate to the `SalesData` table.
• Click Save.

Step 6: Test Dynamic Role

• Click Modeling → View As Roles.
• In the "View as roles" window:
  • Check the box next to DynamicRLS.
  • Select Other user.
  • In the text box, type an email ID that exists in your Users table (e.g., neha@abc.com if Neha is mapped to a specific region).
• Click OK.
• Observe your report visuals and data view.

✅ You should now see only rows in `SalesData` that match the region associated with the email ID you typed. For instance, if neha@abc.com is mapped to the "West" region in your Users table, you will only see "West" region sales.

🔍 What We Are Doing:

Dynamic RLS filters rows based on the actual login email of the user viewing the report. The `USERPRINCIPALNAME()` DAX function fetches the current user's email, and this email is then used to filter the `Users` table. Due to the relationship, this filter flows to the `SalesData` table, showing only the data relevant to that user's region. This method is highly scalable as you only need to manage the `Users` table, not individual roles for every region.

☁️ Part 4: Publish and Assign Roles

Once your RLS is configured in Power BI Desktop, you need to publish the report and assign users to the static roles in the Power BI Service. Dynamic roles don't require explicit user assignment in the service.

Step 7: Publish to Power BI Service

• In Power BI Desktop, click Home → Publish.
• Choose or create a Workspace where you want to publish your report and dataset.
• Click Select.

Step 8: Configure Roles in Service

• Go to Power BI Service (app.powerbi.com).
• Navigate to your Workspace.
• Go to the Datasets + dataflows tab.
• Find your dataset and click the three dots (... More options) next to it.
• Select Security.
• Under the EastRegionRole (your static role), click "Add members" and assign specific users or security groups (e.g., john@abc.com). These users will now only see "East" region data.
• For the DynamicRLS role, you typically do not need to assign users here. It works automatically via the user's login email (User Principal Name) when they view the report.

🔍 What We Are Doing:

This step transitions your RLS from the development environment to a real-world usage scenario. We assign users to static roles, while dynamic roles leverage the user's login identity, making the setup scalable and efficient for larger organizations.

🔐 Part 5: Embedding with Token (Theory)

When embedding Power BI reports into custom applications (using the "App-Owns-Data" model), you generate an embed token. This token allows your application to control which data a user sees, overriding the default Power BI Service security if needed.

You must pass the correct role names and a `username` (which corresponds to the `USERPRINCIPALNAME()` in your DAX) within the embed token's `identities` array. This enforces the RLS defined in your Power BI Desktop file.

Example Embed Token Payload (JSON):

{
  "accessLevel": "View",
  "identities": [
    {
      "username": "neha@abc.com", // This will be passed to USERPRINCIPALNAME()
      "roles": ["DynamicRLS"], // The role(s) to apply for this user
      "datasets": ["your-dataset-id"] // The ID of the dataset this RLS applies to
    }
  ]
}

This JSON payload is sent to the Power BI REST API to generate an embed token. The `username` in the token will be used by the `USERPRINCIPALNAME()` function in your RLS DAX, and the `roles` array specifies which RLS roles should be applied.

🚀 Bonus: Advanced Dynamic RLS with LOOKUPVALUE

Sometimes, the mapping between the user's email and the data filter might not be as direct. For instance, if the `Region` column isn't directly available in your `SalesData` table, or if you need to perform a more complex lookup, you can use `LOOKUPVALUE` or `RELATED` functions in your RLS DAX.

Using LOOKUPVALUE:

If `Region` is only in the `Users` table and you need to filter `SalesData` based on a lookup from `Users`:

LOOKUPVALUE(Users[Region], Users[EmailID], USERPRINCIPALNAME()) = SalesData[Region]

Using RELATED() (if there's a direct relationship):

If your `SalesData` table has a direct relationship to a dimension table (e.g., `DimRegion`) which in turn is related to `Users`, you can use `RELATED()`:

SalesData[EmployeeID] = RELATED(Users[EmployeeID]) && Users[EmailID] = USERPRINCIPALNAME()

(Note: The exact DAX depends on your specific model structure and relationships.)

📘 Practice Task for Learners

To solidify your understanding of Dynamic RLS, try this practice task:

• Create a new Power BI Desktop file.
• Add two new tables (you can manually enter data or create small Excel sheets):
  • Departments: With columns like DepartmentID, DepartmentName.
  • Employees: With columns like EmployeeID, EmployeeName, DepartmentID, and EmailAddress.
• Implement Dynamic RLS so that each department manager (identified by their EmailAddress) sees only their team's data.
• Test it using "View As Roles" in Power BI Desktop.

This exercise will help you apply the concepts learned in a slightly different scenario, reinforcing your RLS skills. Happy securing!

Quick Knowledge Check

Q1. A sales report has three regional managers: North, South, and East. Each manager should only see their region's data. Should you use static or dynamic RLS, and how many roles do you need?

Show Answer

Static RLS — 3 roles. Create three roles in Power BI Desktop: "North Manager" with filter [Region] = "North", "South Manager" with [Region] = "South", and "East Manager" with [Region] = "East". Assign each manager to their respective role in Power BI Service. Static RLS is appropriate here because the filter value is fixed per role. Dynamic RLS would be better if you had 100 managers and maintained a UserPermissions table — it scales without creating a new role per person.

Q2. In a dynamic RLS setup, you add a DAX filter [Email] = USERNAME() to a role called "UserFilter." A user signs in as bob@contoso.com. What does Power BI return?

• A) All rows where Email = "bob@contoso.com"
• B) All rows because USERNAME() returns the admin account, not the end user
• C) No rows because the UserFilter role has no members assigned
• D) An error because USERNAME() is not a valid Power BI DAX function

Show Answer

A. USERNAME() returns the currently logged-in user's UPN (bob@contoso.com in Power BI Service). The DAX filter restricts rows to those where the Email column matches. If the table has a row for bob@contoso.com, he sees it; other users' rows are hidden. Note: in app-owns-data embedding, the "current user" is the effective identity you pass in the embed token, not the service principal's identity.

Q3. How do you test that your RLS roles work correctly in Power BI Desktop before publishing to Service?

Show Answer

Use "View as role" in the Modeling tab. Click Modeling → Manage roles → select a role → click "View as role." Power BI Desktop applies the role's DAX filter to all visuals on the page, showing exactly what that role sees. You can test with specific usernames by entering them in the "Other user" field for dynamic RLS. Always test RLS in Desktop before publishing — bugs in DAX filters are much harder to diagnose after the model is deployed.

5 Things to Remember

• Static RLS = fixed filter per role — one role per data subset. Good for small, fixed groups. Assign users to roles in Power BI Service after publishing.
• Dynamic RLS = filter using USERNAME() — one role for all users; a permission table maps email to allowed data. Scales to thousands of users without creating new roles.
• Roles are defined in Desktop, membership in Service — create and define DAX filters in Power BI Desktop; assign users (or security groups) to roles in the Service workspace.
• "View as role" tests RLS in Desktop — always verify your filter before publishing. Use the "Other user" field to test dynamic RLS with specific email addresses.
• RLS only restricts report viewers — workspace Members and Admins bypass RLS. Only users with the Viewer role and assigned dataset role get filtered data.

📚 Resources

• Power BI Advanced Technology Topic on RR Skillverse →
• Power BI Skill Cards — Visual Cheat Sheets →
• Row-Level Security in Power BI — Microsoft Learn →