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

Welcome to this hands-on guide on implementing Row-Level Security (RLS) in Power BI! Our objective is to implement both Static and Dynamic RLS, 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, adhering to data governance and privacy requirements.

🧪 Designing for RLS with User-Owns-Data Model

The "User-Owns-Data" model is the most common scenario for internal Power BI users. In this setup, each user logs into Power BI Service with their own organizational credentials, and RLS is automatically applied based on their identity.

✅ What is User-Owns-Data?

In this model, the end-user directly interacts with Power BI Service (or embedded reports in Teams/SharePoint) using their own Azure Active Directory (AAD) account. Power BI authenticates them and applies any RLS roles they are assigned to.

✅ How RLS Works Natively:

  • Power BI knows who is logged in via the DAX function USERPRINCIPALNAME().
  • This function returns the signed-in user's User Principal Name (UPN), which is typically their email address (e.g., john.doe@yourcompany.com).
  • The dataset then filters rows based on this UPN, usually by referencing a mapping table (like our `Users` table) that links UPNs to specific data attributes (e.g., regions, departments).

🔧 Step-by-Step: Implementing User-Owns-Data RLS

1. Prepare the Dataset:

You'll need an Excel file (e.g., RLS_Demo_Data.xlsx) with at least 25 entries. This file should contain two sheets:

  • Sales table: Columns like Region, SalesRep, Amount.
  • Users table: Columns like UserEmail (containing UPNs/emails of your test users), Region, Role (optional, for more complex scenarios).

2. Load Data in Power BI Desktop

  • Open Power BI Desktop.
  • Click Home → Get Data → Excel workbook.
  • Select your RLS_Demo_Data.xlsx file.
  • In the Navigator, select both the Sales and Users tables.
  • Click Load.
  • Go to the Model View (left panel). Create a relationship between Sales.Region and Users.Region. Ensure the cross-filter direction is single, from `Users` to `Sales`.

3. Create Role in Power BI Desktop

  • Go to Modeling tab → Manage Roles.
  • Click Create to add a new role. Name it: RegionalRole.
  • Under the "Tables" section, select the Users table.
  • In the DAX filter expression box, add the following DAX: 
    [UserEmail] = USERPRINCIPALNAME()
    This DAX expression restricts access based on the currently logged-in user's email, effectively filtering the `Users` table, which then filters the `Sales` table via the relationship.
  • Click Save.

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

5. Test in Power BI Service

  • Go to Power BI Service (app.powerbi.com).
  • Navigate to your Workspace → Datasets + dataflows tab.
  • Find your published dataset and click the three dots (... More options) next to it.
  • Select Security.
  • Under the RegionalRole, add the email addresses of the users you want to test (e.g., raushan@abc.com, ritu@abc.com). These users must have a Power BI Pro license.
  • To test, click the three dots (...) next to a user you added, and select "Test as role". Alternatively, you can click "View As" at the top of the Security page and select the role and optionally type "Other user" to simulate a specific email.

✅ You should now see only the data relevant to the user whose email you entered or selected, demonstrating dynamic RLS in action.

💻 Designing for RLS with App-Owns-Data Model

The "App-Owns-Data" model is used when you embed Power BI reports into your own custom web application. In this scenario, your application handles user authentication, and the end-users may not even have a Power BI license or direct access to Power BI Service.

✅ What is App-Owns-Data?

Your web application hosts and displays Power BI content. The end-users of your application interact with the reports without directly logging into Power BI. Your application acts as an intermediary, fetching the Power BI content and displaying it.

🤔 Challenge:

Since the end-user doesn't directly log into Power BI, the USERPRINCIPALNAME() DAX function won't return their actual identity. It will return the identity of the service principal or master account that your application uses to access Power BI.

💡 Solution:

You simulate the user identity and apply RLS manually via an **Embed Token**. Your application generates this token, specifying which RLS roles and what "username" (which will be used by `USERPRINCIPALNAME()`) should be applied for the current user.

🔧 Step-by-Step: Setting Up App-Owns-Data with RLS

1. Design Dataset with RLS Role in Power BI Desktop

For this model, you typically define RLS roles with static filters or use a combination of static and dynamic based on your application's logic. Let's use a static role for this example:

  • In Power BI Desktop, go to Modeling → Manage Roles.
  • Add a new role (e.g., EastRegionRole).
  • Apply a static filter on the Sales table: 
    [Region] = "East"
  • Click Save.

2. Publish Dataset to Power BI Service

  • Publish your report and dataset to a workspace in Power BI Service. This workspace must be backed by a Power BI Premium (P SKU) or Power BI Embedded (A SKU) capacity.

3. Register Your App in Azure AD

  • Go to the Azure Portal → App Registrations.
  • Click New registration.
  • Provide a Name for your application (e.g., "PowerBIEmbedDemo").
  • For "Supported account types," choose the appropriate option for your scenario.
  • For "Redirect URI," select "Web" and add a URI where your application will receive authentication responses (e.g., https://localhost:5000/signin-oidc for local development).
  • Click Register. Note down the Application (client) ID and Directory (tenant) ID.
  • Go to Certificates & secrets and create a new client secret. Note its value (it will only be shown once).

4. API Permissions

  • In your App Registration, go to API permissions.
  • Click Add a permission.
  • Add Microsoft Graph: User.Read (Delegated permission).
  • Add Power BI Service (under "APIs my organization uses" or "Microsoft APIs"):
    • Dataset.Read.All
    • Report.Read.All
    • Workspace.Read.All
    • Embed.Read.All
    • (For "App Owns Data" with a Service Principal, you'll typically use Application permissions for these.)
  • Click "Grant admin consent for [Your Tenant]".

5. Create a Service Principal (or use a Master User Account)

  • Enable service principals in your Power BI tenant settings (Power BI Admin Portal).
  • Assign the service principal (the Azure AD App Registration you created) as an Admin or Contributor to the Power BI workspace where your reports are published. This grants it permissions to access the content.

🔐 PART 5: Generating Embed Tokens with RLS

An embed token is a secure, temporary key that lets your application access Power BI content with specific permissions and, crucially, apply RLS.

✅ What is an Embed Token?

It's a JWT (JSON Web Token) generated by the Power BI REST API. Your application requests this token, specifying which report, dataset, and RLS roles should be applied for the current user.

📦 Embed Token Payload Example:

When your application calls the Power BI REST API to generate an embed token, it sends a payload like this. This example applies the "EastRegionRole" and simulates a user "neha@abc.com": 

{
  "accessLevel": "View", // Can be View, Edit, Create
  "identities": [
    {
      "username": "raushan@abc.com", // This value is passed to USERPRINCIPALNAME() in your DAX
      "roles": ["EastRegionRole"], // Must match the RLS role(s) you defined in Power BI Desktop
      "datasets": ["e0f12345-6789-abc0-def1-23456789abcd"] // The ID of the dataset this RLS applies to (from Power BI Service URL)
    }
  ]
}

🔍 Explanation of Payload Fields:

  • username: This is a string that represents the user's identity from your own application. Power BI's USERPRINCIPALNAME() DAX function will return this value when the report is viewed with this token.
  • roles: An array of strings specifying the exact names of the RLS roles (defined in Power BI Desktop) that should be applied for this user.
  • datasets: An array of dataset IDs. This is crucial for Power BI to know which dataset the RLS roles should be applied to. You can find the dataset ID in the Power BI Service URL when viewing a dataset.

🛠 Implementation in Your Web App (JavaScript SDK)

Once your backend application generates the embed token, it passes it to your frontend (browser-based) application. The Power BI JavaScript SDK then uses this token to embed the report: 

import * as powerbi from 'powerbi-client';
import models from 'powerbi-client/dist/models';

// Assume reportContainer is a div element in your HTML
const reportContainer = document.getElementById('reportContainer');

// Assume embedToken, embedUrl, and reportId are retrieved from your backend
const embedToken = '<<YourGeneratedEmbedToken>>';
const embedUrl = 'https://app.powerbi.com/reportEmbed?reportId=<<YourReportId>>';
const reportId = '<<YourReportId>>';

powerbi.embed(reportContainer, {
  type: 'report',
  tokenType: models.TokenType.Embed,
  accessToken: embedToken,
  embedUrl: embedUrl,
  id: reportId,
  settings: {
    filterPaneEnabled: false,
    navContentPaneEnabled: false
  }
});

✅ Summary and Real-World Analogy

To summarize the two RLS models:

  • User-Owns-Data: This is like giving each employee their personal locker with access granted via their **fingerprint** (their Power BI login). The system recognizes them directly.
  • App-Owns-Data: This is like hosting a private museum where visitors don't have keys, but you (the application) issue them a **special ticket** (the embed token). This ticket specifies exactly which exhibits they are allowed to see, based on your application's logic.

📚 Practice Plan

To truly grasp these concepts, hands-on practice is key. Follow this plan:

Part A: User-Owns-Data

  • Implement RLS using email mapping in Power BI Desktop (as described in Part 3).
  • Publish the report to Power BI Service.
  • Test access for different users using the "View As" feature in Power BI Service.

Part B: App-Owns-Data

  • Create RLS roles with static filters in Power BI Desktop (as described in Part 4, Step 1).
  • Register an app in Azure AD and configure its API permissions.
  • Develop a simple backend (e.g., using C# ASP.NET Core, Node.js, or Python) to generate embed tokens with role identities.
  • Create a simple web page (HTML/JavaScript) to embed the Power BI report using the generated token and test different RLS scenarios.

Deliverables for Your Portfolio:

  • ✅ A working RLS demo .pbix file.
  • ✅ An embed token generation script (e.g., in C# or Node.js).
  • ✅ A working demo of the embedded report in a web application.

Let me know if you want me to generate sample code for token generation or a full working Blazor/ASP.NET embed project to help you with Part B!

Quick Knowledge Check

Q1. In a multi-tenant SaaS application using app-owns-data embedding, how do you isolate one customer's data from another when all tenants share a single Power BI dataset?

Show Answer

Use dynamic RLS with a TenantID column and pass the tenant's ID as the effective identity in the embed token. The RLS role has a DAX filter like [TenantID] = USERNAME(). When generating the embed token for Tenant A, set the effective identity to Tenant A's ID. Power BI applies the filter, returning only Tenant A's rows. Each tenant's report is isolated through the embed token — no need for separate datasets per tenant. This is the recommended ISV pattern for cost-effective multi-tenant embedding.

Q2. A developer sets up RLS but finds that one user (an analyst) can still see all data regardless of the RLS role. What is the most likely cause?

  • A) The analyst's Power BI account has a Premium licence which bypasses RLS
  • B) The analyst is a workspace Member or Admin — workspace Members and above bypass RLS
  • C) Dynamic RLS is not supported for Import mode datasets
  • D) The RLS role was created in Service instead of Desktop
Show Answer

B. RLS only applies to users with the Viewer role in a workspace. Workspace Members, Contributors, and Admins bypass all RLS rules and see the full dataset. To enforce RLS for an internal user, they must be added as a Viewer to the workspace, not as a Member. Alternatively, share the report directly (not workspace access) and assign them to an RLS role in the dataset settings.

Q3. What is the recommended approach to avoid role assignment maintenance overhead in a large organisation with hundreds of users and dynamic data permissions?

Show Answer

Use a user-permission table in the data model and a single dynamic RLS role. Create a table (e.g., UserPermissions[Email, Region]) that maps each user's email to their allowed data subset. Define ONE role with a DAX filter like [Region] = LOOKUPVALUE(UserPermissions[Region], UserPermissions[Email], USERNAME()). When permissions change, update the UserPermissions table (via dataset refresh) — no changes to roles are needed. This avoids maintaining hundreds of static roles and eliminates the need to re-publish the model for every permission change.

5 Things to Remember
  • Multi-tenant isolation via effective identity — pass TenantID as the effective identity in the embed token. One dataset serves all tenants with data isolated by RLS.
  • Workspace Members bypass RLS — only Viewers (and report-shared users assigned to a role) are filtered. Analysts who need RLS must be workspace Viewers, not Members.
  • User-permission table scales dynamic RLS — one role + a permission lookup table handles hundreds of users. Update the table data to change permissions without republishing the model.
  • LOOKUPVALUE() in DAX for table-driven RLS — maps the current user's identity to their allowed data subset from a permission table.
  • Test with "View as role" + specific user — always verify the permission table and DAX filter produce the correct rows for each user before deploying.
📚 Resources