Dynamic Row-Level Security (RLS) with USERPRINCIPALNAME()

This lesson on Dynamic Row-Level Security (RLS) with USERPRINCIPALNAME() is hands-on and example-driven. You will implement DAX-based Row-Level Security (RLS) to restrict data access based on user roles and identities. You will define security filter expressions in Power BI Desktop, validate them locally with 'View as', and map users or groups within the Power BI Service.

What You'll Be Able To Do

• Define DAX table filter expressions inside the Manage Roles dialog to restrict dimension data.
• Validate security role behavior locally using the View As feature in Power BI Desktop.
• Publish secured semantic models to the Power BI Service and configure role membership.
• Execute Test as role in the Power BI Service to verify live data restriction per user.
• Differentiate workspace edit permissions from viewer permissions to prevent unintended RLS bypasses.

Detailed Concept Walkthrough

1. DAX Security Role Architecture

Row-Level Security (RLS) restricts data access at the row level by applying boolean DAX filter expressions directly to dimension tables. When a role is active, DAX filters propagate through active model relationships to automatically slice fact data.

• Mechanism: Security roles are created within the Power BI Desktop Modeling tab using the Manage Roles window. You apply a DAX boolean expression to a specific table, such as filtering a sales territory column by country name.
• Under the Hood: Power BI injects the role's DAX filter expression into the filter context of every query generated by report visuals. If the expression evaluates to TRUE for a row, that row remains visible; if FALSE, the VertiPaq engine excludes it before aggregation.
• Best Practice: Always apply RLS filters to dimension tables rather than large fact tables. Dimension filters automatically propagate downstream to fact tables across one-to-many relationships, optimizing query performance and simplifying maintenance.

// Define a static DAX filter rule on the Sales Territory dimension
// Applied within Manage Roles > SalesTerritory Table
[Sales Territory Country] = "Germany"

Key Takeaway: RLS rules act as permanent, query-level boolean filters on dimension tables that propagate throughout the entire data model.

2. Local Validation with View As

The 'View as' tool simulates how visuals render for a specific role or user identity before publishing the report. It provides an immediate feedback loop to ensure DAX rules filter data as intended without altering the underlying data model.

• Mechanism: Located on the Modeling tab, the 'View as' dialog allows authors to select one or more defined security roles or specify another user identity. Toggling a role instantly updates the canvas visuals to reflect that role's restricted view.
• Under the Hood: Desktop creates a temporary session-level impersonation context, executing all report DAX queries with the selected role's filter rules applied. A persistent yellow warning banner appears at the top of the canvas indicating active impersonation.
• Best Practice: Always test both positive and negative boundary conditions locally. Confirm that authorized data appears and that unauthorized records (e.g., non-German regions) are completely absent from visuals and aggregate totals.

// Verification logic evaluated by visuals under the Germany role:
// Evaluates to TRUE only for rows where territory matches
EVALUATE
CALCULATETABLE(
    'SalesTerritory',
    'SalesTerritory'[Sales Territory Country] = "Germany"
)

Key Takeaway: Always use 'View as' in Desktop to verify that filter rules accurately isolate intended records before cloud deployment.

3. Service Membership Mapping and Testing

Security role definitions and member assignments are strictly decoupled: roles are authored in Desktop, while users and security groups are assigned in the Power BI Service. The Service provides dedicated testing tools to validate live tenant permissions.

• Mechanism: After publishing the dataset, navigate to the dataset's 'Security' settings in the Service workspace. Enter email addresses or Azure AD security groups into the corresponding role created in Desktop and save.
• Under the Hood: The Power BI Service stores role membership mappings in tenant metadata. When an assigned user queries the report, the Service evaluates their Azure AD identity, identifies their assigned roles, and enforces the corresponding DAX filter rules.
• Execution Flow: Use the ellipsis next to the role name in the Service and click 'Test as role'. This opens a browser session rendering the report strictly through that role's filter context, validating end-to-end cloud enforcement.

# Cloud Security Configuration Workflow:
# 1. Publish .pbix to Workspace
# 2. Go to Dataset > More Options (...) > Security
# 3. Select Role (e.g., 'Germany') > Enter user/group email > Add > Save
# 4. Click (...) next to Role > 'Test as role'

Key Takeaway: DAX role schemas are built in Desktop, but user and group assignments exist entirely within Power BI Service dataset security settings.

4. RLS Permissions and Workspace Architecture

RLS is enforced only for users with read-only access to the data. Workspace collaboration roles with edit capabilities bypass RLS completely by design.

• Mechanism: Users assigned the Workspace Viewer role or consumers accessing the report via an App or shared link are subject to RLS restrictions. Their queries strictly enforce assigned role filters.
• Under the Hood: Users with Admin, Member, or Contributor permissions in a workspace possess edit rights over the dataset. Power BI grants edit-level identities full read access across all rows, ignoring all RLS definitions.
• Best Practice: Distribute reports to standard business users via Power BI Apps or Workspace Viewer roles. Never grant Contributor, Member, or Admin access to users whose data visibility must be restricted by RLS.

// Access Matrix Rule Definition:
// If Workspace Role IN {"Admin", "Member", "Contributor"} -> Bypass RLS
// If Workspace Role == "Viewer" OR Access via App -> Enforce RLS

Key Takeaway: RLS is only enforced for consumers with Viewer permissions; workspace edit roles always bypass security filters.

Topics Covered in Dynamic Row-Level Security (RLS) with USERPRINCIPALNAME()

1. RLS Fundamentals (0:00 - 1:29) — Examines business scenarios contrasting full executive data access with restricted regional sales roles.
2. Defining Security Roles (1:29 - 2:18) — Walks through navigating the Manage Roles dialog and authoring DAX table filters on territory dimensions.
3. Local Role Validation (2:18 - 2:56) — Demonstrates using the View as feature in Power BI Desktop to simulate role-based filtering locally.
4. Service Role Mapping (2:56 - 4:04) — Covers publishing datasets, assigning user accounts in dataset security settings, and running Test as role.
5. RLS Permission Architecture (4:04 - 5:04) — Details how workspace access levels impact RLS enforcement across apps, shared links, and workspaces.

Reference Cheat Sheet

• [Column] = "Value" — Filters table rows where column matches literal value

  [Sales Territory Country] = "Germany"
  

• Modeling > Manage roles — Opens dialog to create roles and DAX rules

• Modeling > View as — Impersonates roles locally inside Power BI Desktop

• Dataset > Security — Maps cloud users and groups to roles

• Security > Test as role — Validates role filtering directly inside Power BI Service

Comparison Table

Common Pitfalls

• Mistake: Granting Member or Contributor workspace roles to users who require restricted access. Avoid: Assign restricted consumers the Viewer workspace role or distribute access via Power BI Apps.
• Mistake: Attempting to assign user email addresses to security roles inside Power BI Desktop. Avoid: Define DAX role schemas in Desktop and assign user email mappings exclusively in Power BI Service.
• Mistake: Applying RLS filters directly to large fact tables instead of related dimensions. Avoid: Define filter rules on dimension tables and allow relationship cross-filtering to restrict fact rows.

FAQs

• Why can a user see all data even after being added to an RLS role? The user likely has Admin, Member, or Contributor permissions in the workspace, which automatically bypasses RLS. Change their workspace permission to Viewer or share via an App.
• Can I assign Azure Active Directory security groups to RLS roles? Yes, entering security groups or distribution lists in the Power BI Service dataset security settings is supported and recommended for scalable role management.
• Where are DAX filter expressions written for RLS? DAX filter rules are written exclusively in Power BI Desktop within the Manage Roles dialog under the Modeling tab.
• What happens if a user is not assigned to any RLS role in the Service? If RLS is defined on the dataset and a Viewer user is not assigned to any role, they will encounter an error and cannot view report visuals.