Row-Level Security

What is Row-Level Security?

Row-Level Security (RLS) is a Power BI feature that restricts data access at the row level. When RLS is implemented, different users see different subsets of data in the same report, based on rules you define. A sales manager in the East region sees only East region data, while a manager in the West region sees only West region data — all from the same published report.

Why RLS Matters

In any organization, not everyone should see all data. Consider these scenarios:

• Regional sales data — Sales representatives should see only their territory's numbers, not the entire company's data
• Financial reports — Department heads should see only their department's budget and expenses
• HR dashboards — Managers should see only their direct reports' information
• Multi-tenant applications — External clients should see only their own company's data, not other clients' data
• Compliance requirements — Regulations like GDPR, HIPAA, and SOX may require limiting data access based on user roles

Without RLS, you would need to create separate reports for each audience, leading to content sprawl, maintenance headaches, and a higher risk of data leaks.

RLS in the BI Security Model

RLS operates at the data layer of the Power BI security model. It works alongside other security mechanisms:

Security Layer	What It Controls	Mechanism
Authentication	Who can sign in	Azure Active Directory / Entra ID
Workspace access	Who can see the workspace	Workspace roles (Admin, Member, Contributor, Viewer)
Report/Dashboard sharing	Who can see specific items	Sharing, apps, permissions
Row-Level Security	What data rows a user sees	DAX filter expressions on tables
Object-Level Security	What columns/tables a user sees	Role-based column/table restrictions
Column-Level Security	What columns are accessible	Available in Analysis Services; limited in Power BI

RLS is the last line of defense — even if a user has access to a report, they will only see the rows their role permits.

Compliance and Governance

RLS helps organizations meet compliance requirements by ensuring:

• Data is segmented by user identity automatically
• No manual intervention is required once roles are configured
• Audit trails in Power BI track who accessed what data
• Security rules are defined centrally, not in individual reports
• Changes to security rules propagate immediately to all consumers

---

How RLS Works

At its core, RLS applies DAX filter expressions to tables in the data model. When a user with an assigned role queries the data, Power BI adds the RLS filter to every query behind the scenes.

The Evaluation Flow

1. A user opens a report in Power BI Service
2. Power BI checks which RLS role(s) the user is assigned to
3. The DAX filter expressions for those roles are applied to the relevant tables
4. The filter propagates through relationships to related tables
5. All visuals render showing only the filtered data
6. The user has no ability to bypass or remove the RLS filter

DAX Filter Expressions

An RLS filter expression is a DAX expression that returns TRUE or FALSE for each row. Only rows where the expression returns TRUE are visible to the user.

Example: A simple static filter for the East region:

[Region] = "East"

This expression is applied to a specific table (e.g., the Sales table). Every query against the Sales table will include this filter, and only rows where Region equals "East" will be returned.

Filter Propagation Through Relationships

RLS filters propagate through relationships based on the model's cross-filter direction:

• In a one-to-many relationship with single cross-filter direction, a filter on the "one" side (dimension table) automatically filters the "many" side (fact table)
• For example, filtering the Region table to "East" will automatically filter the Sales fact table to show only East region sales
• Filters do not propagate in the reverse direction by default (many-to-one is blocked)
• Bi-directional cross-filtering can be enabled, but requires careful consideration for security

How Multiple Roles Interact

If a user is assigned to multiple roles, the filters combine with OR logic — the user sees the union of all data permitted by their assigned roles. For example:

• Role A filters Region = "East"
• Role B filters Region = "West"
• A user assigned to both roles sees East AND West data

This OR behavior cannot be changed to AND logic. If you need restrictive (AND) behavior, design a single role with combined logic.

---

Static RLS

Static RLS uses fixed values in the DAX filter expressions. You explicitly define which data each role can see.

Creating Roles in Desktop

Step 1: Open your report in Power BI Desktop.

Step 2: Navigate to the Modeling tab on the ribbon.

Step 3: Click Manage Roles.

Step 4: Click + New to create a new role.

Step 5: Give the role a meaningful name (e.g., "East Region").

Step 6: Select the table to apply the filter to (e.g., "DimRegion" or "Sales").

Step 7: Write the DAX filter expression:

[Region] = "East"

Step 8: Click the checkmark to validate the expression.

Step 9: Repeat for additional roles (e.g., "West Region" with [Region] = "West").

Step 10: Click Save.

Step-by-Step Example — Region-Based Access

Consider a sales dataset with the following tables:

Sales Table:

OrderID	ProductID	Region	Amount
1001	P01	East	500
1002	P02	West	750
1003	P01	East	300
1004	P03	North	600
1005	P02	South	450
1006	P01	West	800

Creating Static Roles:

Role: "East Region"

• Table: Sales
• Filter expression:

[Region] = "East"

Role: "West Region"

• Table: Sales
• Filter expression:

[Region] = "West"

Role: "North Region"

• Table: Sales
• Filter expression:

[Region] = "North"

Role: "South Region"

• Table: Sales
• Filter expression:

[Region] = "South"

Assigning Users to Roles in Service

After publishing the report to Power BI Service:

Step 1: Navigate to the workspace containing the dataset.

Step 2: Click the ellipsis (...) next to the semantic model (dataset) (not the report).

Step 3: Select Security.

Step 4: Select a role from the left panel.

Step 5: Enter email addresses or security group names in the Members field.

Step 6: Click Add.

Step 7: Repeat for each role.

Important: Always assign users to roles on the dataset, not the report. RLS is a dataset-level feature.

Multiple Filters in a Static Role

A single role can have filter expressions on multiple tables:

Role: "East Sales Only"

• Table: Sales, Filter: [Region] = "East"
• Table: Products, Filter: [Category] = "Electronics"

When multiple tables are filtered within a single role, the filters combine with AND logic — the user sees only East region sales for Electronics products.

Static RLS with Multiple Values

You can use DAX functions to filter for multiple values in a single role:

[Region] IN { "East", "West" }

Or using OR logic:

[Region] = "East" || [Region] = "West"

Limitations of Static RLS

Limitation	Impact
Manual role creation	You must create a separate role for each filter combination
Maintenance overhead	Adding a new region requires creating a new role and reassigning users
Scalability	Impractical for large organizations with many permutations
Role explosion	Many roles become difficult to manage
No dynamic adaptation	Roles do not automatically adjust when organizational structure changes

For these reasons, dynamic RLS is usually preferred for production deployments.

---

Dynamic RLS

Dynamic RLS uses the identity of the logged-in user to determine which data they can see. Instead of creating a separate role for each set of values, you create a single role that references a security table mapping users to their permitted data.

The Concept

1. Create a security mapping table that maps user email addresses to their permitted data (e.g., region, department)
2. Create a relationship between the security table and the data tables
3. Create a single RLS role that filters the security table using USERPRINCIPALNAME()
4. When a user opens the report, USERPRINCIPALNAME() returns their email address
5. The filter on the security table propagates to all related data tables

Setting Up the Security Table

Create a table (in your data source or via Power Query) with the following structure:

UserSecurity Table:

UserEmail	Region
alice@company.com	East
bob@company.com	West
carol@company.com	North
dave@company.com	South
eve@company.com	East
eve@company.com	West

Notice that Eve has two entries, giving her access to both East and West regions.

Creating the Relationship

Step 1: In Power BI Desktop, go to the Model view.

Step 2: Create a relationship between the UserSecurity table and the data table:

• From: UserSecurity[Region]
• To: DimRegion[Region] (or directly to the Sales table if no region dimension exists)

Step 3: Set the relationship properties:

• Cardinality: Many-to-many (if mapping to a dimension) or Many-to-one
• Cross filter direction: Consider the data model topology carefully

Step 4: Ensure the filter can propagate from UserSecurity through the relationships to the fact table.

Writing the DAX Filter

Step 1: Open Manage Roles (Modeling tab).

Step 2: Create a new role called "Dynamic Region Security" (or similar).

Step 3: Select the UserSecurity table.

Step 4: Enter the DAX filter expression:

[UserEmail] = USERPRINCIPALNAME()

Step 5: Validate and save.

How It Works End-to-End

1. Alice (alice@company.com) opens the report
2. Power BI evaluates USERPRINCIPALNAME() and returns "alice@company.com"
3. The filter [UserEmail] = "alice@company.com" is applied to the UserSecurity table
4. Only the row with Region = "East" remains in UserSecurity
5. The filter propagates through the relationship to DimRegion, filtering it to "East"
6. The filter propagates from DimRegion to the Sales fact table
7. All visuals show only East region data

When Eve opens the same report:

1. USERPRINCIPALNAME() returns "eve@company.com"
2. Two rows remain in UserSecurity (East and West)
3. The filter propagates, showing data for both East and West regions

Complete Dynamic RLS Example

Data Model:

UserSecurity (UserEmail, Region)
    └── DimRegion (Region, RegionName, RegionManager)
            └── FactSales (OrderID, ProductID, RegionKey, Amount, Date)

UserSecurity Table:

UserEmail	Region
alice@contoso.com	East
alice@contoso.com	Southeast
bob@contoso.com	West
carol@contoso.com	North
carol@contoso.com	Northeast
carol@contoso.com	Northwest
dave@contoso.com	South

RLS Role: "RegionalAccess"

• Table: UserSecurity
• Filter:

[UserEmail] = USERPRINCIPALNAME()

Result: Alice sees East and Southeast data. Bob sees West data. Carol sees North, Northeast, and Northwest data. Dave sees South data.

---

Dynamic RLS with Multiple Levels

Real-world security requirements often involve hierarchical access — managers should see data for their entire team, directors should see data for all managers under them, and so on.

Manager Hierarchy

Scenario: A manager should see data for all employees in their reporting chain.

ManagerHierarchy Table:

ManagerEmail	EmployeeEmail
manager1@company.com	emp1@company.com
manager1@company.com	emp2@company.com
manager1@company.com	emp3@company.com
director1@company.com	manager1@company.com
director1@company.com	manager2@company.com

Challenge: This flat structure only shows one level. A director would see data for their direct reports (managers) but not for the managers' employees.

Solution: Use a flattened hierarchy table that includes all indirect reports:

FlattenedHierarchy Table:

SupervisorEmail	SubordinateEmail
manager1@company.com	emp1@company.com
manager1@company.com	emp2@company.com
manager1@company.com	emp3@company.com
director1@company.com	manager1@company.com
director1@company.com	manager2@company.com
director1@company.com	emp1@company.com
director1@company.com	emp2@company.com
director1@company.com	emp3@company.com
director1@company.com	emp4@company.com
director1@company.com	emp5@company.com
emp1@company.com	emp1@company.com
emp2@company.com	emp2@company.com

Note: Include self-referencing rows (e.g., emp1 > emp1) so employees can see their own data.

RLS Role:

• Table: FlattenedHierarchy
• Filter:

[SupervisorEmail] = USERPRINCIPALNAME()

Relationship: FlattenedHierarchy[SubordinateEmail] connects to EmployeeDimension[EmployeeEmail].

Geography Hierarchy

Scenario: A country manager should see all regions in their country; a regional manager sees only their region.

GeoSecurity Table:

UserEmail	Country	Region
country_mgr@company.com	USA	*
east_mgr@company.com	USA	East
west_mgr@company.com	USA	West
uk_mgr@company.com	UK	*

For the asterisk (*) wildcard approach, use a DAX filter that checks for the wildcard:

[UserEmail] = USERPRINCIPALNAME()

And in the data model, ensure that the country manager has a row for every region in their country. Alternatively, use two roles — one for country-level and one for region-level — and create the security table accordingly.

Organization Unit Hierarchy

Similar to the manager hierarchy, but based on organizational units (departments, divisions, business units). The security table maps users to org units, and a separate hierarchy table maps org units to parent org units. A flattened version of the hierarchy provides all ancestor/descendant relationships.

---

USERPRINCIPALNAME vs USERNAME

Power BI provides two DAX functions for identifying the current user:

USERPRINCIPALNAME()

Returns the User Principal Name (UPN) of the currently logged-in user. The UPN is typically the user's email address in Azure Active Directory / Entra ID.

USERPRINCIPALNAME()
-- Returns: "alice@contoso.com"

USERNAME()

Returns the user identity in DOMAIN\username format (for on-premises Active Directory) or UPN format (for cloud-only identities).

USERNAME()
-- Returns: "CONTOSO\alice" (on-premises)
-- or: "alice@contoso.com" (cloud)

Comparison Table

Feature	USERPRINCIPALNAME()	USERNAME()
Return format	Always UPN (email)	DOMAIN\username or UPN
Consistency	Consistent across environments	Varies by identity source
Cloud identities	Returns UPN	Returns UPN
On-premises identities	Returns UPN	Returns DOMAIN\username
Recommended for RLS	Yes	No (use only for legacy scenarios)
Works with Embedded	Yes (with effective identity)	Yes (with effective identity)
Case sensitivity	Case-insensitive in comparisons	Case-insensitive in comparisons

Recommendation

Always use USERPRINCIPALNAME() for RLS in Power BI. It provides consistent behavior regardless of whether the identity is cloud-only, hybrid, or synchronized from on-premises Active Directory.

CUSTOMDATA() for Embedded Scenarios

When embedding Power BI reports in custom applications using the Power BI Embedded API, you can pass custom data with the embed token:

[TenantID] = CUSTOMDATA()

Use case: Multi-tenant applications where the embed token includes the tenant ID, and the RLS filter restricts data to that tenant. This is useful when the embedding application manages its own authentication and user identity.

---

Testing RLS

Testing is critical to ensure that RLS is working correctly. Power BI provides built-in testing tools in both Desktop and Service.

Testing in Power BI Desktop

Step 1: Open your report in Power BI Desktop.

Step 2: Navigate to the Modeling tab.

Step 3: Click View as (previously called "View as Roles").

Step 4: Select the role(s) you want to test.

Step 5: Optionally, enter a value for Other user to simulate a specific user's UPN (useful for testing dynamic RLS with USERPRINCIPALNAME()).

Step 6: Click OK.

Step 7: The report now renders as if you were the specified user with the selected role.

Step 8: A yellow banner at the top of the report indicates that you are viewing the report with RLS applied.

Step 9: Click Stop viewing to return to the full data view.

Testing in Power BI Service

Step 1: Navigate to the workspace containing the dataset.

Step 2: Click the ellipsis (...) next to the semantic model (dataset).

Step 3: Select Security.

Step 4: Select a role from the left panel.

Step 5: Click Test as role at the top of the page.

Step 6: The report opens in a new tab with the selected role applied.

Step 7: Optionally, click Now viewing as and select Test as a specific person to enter a user's email address.

Step 8: Verify that the data shown matches the expected access for that role/user.

Common Testing Mistakes

Mistake	Consequence	Prevention
Testing only in Desktop	Missing Service-specific behaviors	Always test in both Desktop and Service
Testing with admin account	Admins bypass RLS and see all data	Test with non-admin accounts or use "Test as role"
Not testing edge cases	Users at boundaries may see wrong data	Test with users who should see zero rows, one row, and multiple rows
Forgetting to assign users	Users see all data (no RLS applied)	Verify user-role assignments in Service
Testing only one role	Multi-role interactions may cause unexpected access	Test users assigned to multiple roles
Not testing after model changes	Model changes may break RLS	Re-test RLS after every model update

Testing Checklist

Use this checklist to verify your RLS implementation:

• Each role shows only the expected data rows
• Users assigned to a role see the correct data in Service
• Users not assigned to any role see all data (default behavior — ensure all users are assigned)
• Users assigned to multiple roles see the union (OR) of permitted data
• Dynamic RLS correctly identifies each user via USERPRINCIPALNAME()
• Filter propagation through relationships works correctly
• Edge cases: users with no matching security table entry see zero rows
• Edge cases: new employees/regions are handled by the security table
• Performance is acceptable with RLS applied (test with large datasets)
• Report visuals display correctly (no errors or blank visuals)

---

RLS with Relationships

The way RLS interacts with your data model's relationships is one of the most important (and most misunderstood) aspects of the feature.

Filter Flow in One-to-Many Relationships

In a standard star schema with single-direction cross-filtering:

DimRegion (1) ──→ (Many) FactSales

• A filter on DimRegion propagates downstream to FactSales
• A filter on FactSales does not propagate upstream to DimRegion

For RLS: Place the filter expression on the dimension table (DimRegion). The filter automatically propagates to the fact table (FactSales) and any other tables connected downstream.

Bi-Directional Security Filtering

In some models, you may need the filter to flow in both directions. For example:

UserSecurity (Many) ──↔── (Many) DimRegion (1) ──→ (Many) FactSales

If the UserSecurity-to-DimRegion relationship is many-to-many, you may need to enable bi-directional filtering for the security filter to propagate correctly.

To enable bi-directional security filtering:

Step 1: In the Model view, double-click the relationship between UserSecurity and DimRegion.

Step 2: Set Cross filter direction to Both.

Step 3: Check the box Apply security filter in both directions.

Important: Enabling bi-directional filtering for all relationships is not recommended as it can cause ambiguous filter paths and performance issues. Enable it only where necessary for security propagation.

Many-to-Many RLS Challenges

Many-to-many relationships introduce complexity for RLS:

Challenge	Description	Mitigation
Ambiguous filter paths	Multiple paths for filter propagation	Carefully design relationships; use bridge tables
Performance impact	Bi-directional filters increase query complexity	Minimize bi-directional relationships
Unexpected results	Filters may propagate in unintended ways	Test thoroughly with different user scenarios
Blank rows	Unmatched keys may produce blank entries	Ensure referential integrity or handle blanks

Recommended Relationship Pattern for Dynamic RLS

The most reliable pattern for dynamic RLS:

UserSecurity (Many) ──→ (1) DimRegion (1) ──→ (Many) FactSales

1. UserSecurity contains multiple rows per user (one per region)
2. UserSecurity has a many-to-one relationship to DimRegion
3. The RLS filter on UserSecurity ([UserEmail] = USERPRINCIPALNAME()) filters to the user's rows
4. The filter propagates to DimRegion (many-to-one, single direction)
5. DimRegion filters FactSales (one-to-many, single direction)

This pattern avoids bi-directional filtering and many-to-many complications.

---

Object-Level Security (OLS)

While RLS restricts which rows a user can see, Object-Level Security (OLS) restricts which columns or tables a user can see.

What OLS Does

OLS allows you to hide specific columns or entire tables from certain roles. Users assigned to a role with OLS restrictions:

• Cannot see the hidden column in any visual
• Cannot reference the hidden column in DAX queries
• Receive an error if they attempt to access the column through any means
• See the report with those visuals that reference hidden columns either hidden or showing an error

Setting Up OLS with Tabular Editor

OLS cannot be configured directly in Power BI Desktop. You need an external tool like Tabular Editor:

Step 1: Open your report in Power BI Desktop.

Step 2: Launch Tabular Editor from the External Tools ribbon tab.

Step 3: In Tabular Editor, navigate to Roles in the model tree.

Step 4: Select the role you want to configure.

Step 5: In the role's properties, expand Table Permissions.

Step 6: For each column or table you want to restrict:

• Set the permission to None (completely hidden) or Read (visible)

Step 7: Save the changes back to the Power BI Desktop model.

Step 8: Publish the report to Power BI Service.

OLS Permissions

Permission	Effect
Default	Column/table is visible (inherits from table permission)
None	Column/table is completely hidden and inaccessible
Read	Column/table is visible and accessible

Use Cases for OLS

• Salary data — Hide salary columns from non-HR roles
• PII (Personally Identifiable Information) — Hide Social Security numbers, addresses, or phone numbers from roles that do not need them
• Financial details — Hide profit margin columns from operational roles
• Competitive data — Hide cost-of-goods columns from sales roles

OLS Limitations

Limitation	Details
Cannot configure in Desktop	Requires Tabular Editor or XMLA endpoints
Affects visuals	Visuals referencing hidden columns will show errors
No partial visibility	A column is either fully visible or fully hidden for a role
Testing complexity	Requires testing each role to verify correct visibility
Report design impact	Reports must be designed to gracefully handle hidden columns

---

RLS Best Practices

Design Principles

1. Prefer dynamic RLS over static RLS — Dynamic RLS scales better and requires less maintenance. A single role with USERPRINCIPALNAME() handles all users automatically.

2. Maintain a security mapping table — Keep a dedicated table mapping user emails to their access levels. Store this table in a database or SharePoint list for easy maintenance.

3. Use security groups when possible — Instead of assigning individual users to roles, assign Azure AD security groups. Group membership changes automatically propagate.

4. Test thoroughly — Test with multiple users representing different access levels, edge cases (no access, full access, multi-region access), and after every model change.

5. Document roles and filters — Maintain documentation of all roles, their filter expressions, and the business logic behind them.

6. Minimize bi-directional filtering — Use single-direction relationships where possible. Only enable bi-directional security filtering when absolutely necessary.

7. Keep filter expressions simple — Complex DAX in RLS filters impacts performance. If the logic is complex, push it into the data model (e.g., a calculated column or a pre-computed mapping table).

8. Plan for organizational changes — Design your security model to accommodate new regions, departments, or organizational units without requiring role changes.

Performance Considerations

Factor	Impact	Recommendation
Number of RLS roles	Minimal impact	Keep roles manageable for maintenance, not performance
Complexity of DAX filter	High impact	Avoid complex expressions; use simple column comparisons
Security table size	Moderate impact	Keep the security table lean; remove inactive users
Number of filtered tables	Moderate impact	Filter at the dimension level and let propagation handle fact tables
Bi-directional relationships	High impact	Avoid unless necessary for security propagation
Large cardinality in filter column	Moderate impact	Use integer keys instead of long text strings

Maintenance Checklist

• Review security table quarterly for accuracy
• Remove terminated employees promptly
• Add new employees and their access levels
• Test RLS after every data model change
• Audit user-role assignments in Power BI Service
• Monitor for users with no role assignment (they see all data)
• Keep documentation updated

---

Common RLS Patterns

Pattern 1 — Regional Access

The most common pattern. Users see data only for their assigned region(s).

Security Table: UserEmail, Region

Filter:

[UserEmail] = USERPRINCIPALNAME()

Use case: Sales teams, field operations, territorial management.

Pattern 2 — Department-Based Access

Users see data only for their department.

Security Table: UserEmail, Department

Filter:

[UserEmail] = USERPRINCIPALNAME()

Use case: HR reporting, departmental budgets, cost center management.

Pattern 3 — Manager-Subordinate Hierarchy

Managers see their own data plus all subordinates' data. The hierarchy is flattened into a security table.

Security Table: ManagerEmail, EmployeeID

Filter:

[ManagerEmail] = USERPRINCIPALNAME()

Use case: People analytics, sales team performance, direct report management.

Pattern 4 — Multi-Tenant Security

External clients each see only their own data. Common in ISV (Independent Software Vendor) and consulting scenarios.

Security Table: UserEmail, TenantID (or ClientID)

Filter:

[UserEmail] = USERPRINCIPALNAME()

Or with embedded scenarios:

[TenantID] = CUSTOMDATA()

Use case: SaaS applications, consulting firms sharing data with multiple clients.

Pattern 5 — Time-Based Access

Users see data only for specific time periods (e.g., current quarter plus one prior quarter).

Security Table: UserEmail, AllowedStartDate, AllowedEndDate

Filter (on the date dimension):

RELATED(UserSecurity[AllowedStartDate]) <= [Date]
  && RELATED(UserSecurity[AllowedEndDate]) >= [Date]

Use case: Compliance scenarios, trial access, time-limited partnerships.

Pattern 6 — Full Access Role

A role for administrators or executives who should see all data.

Filter (on any table):

TRUE()

A filter expression of TRUE() returns TRUE for every row, effectively granting access to all data. Assign this role to admin users.

Use case: Executives, BI administrators, data stewards.

---

RLS Limitations

Understanding the limitations of RLS is essential to avoid security gaps and unexpected behavior.

Key Limitations

Limitation	Details
Admins bypass RLS	Workspace Admins and Members always see all data, regardless of role assignment
Does not restrict dashboards	Dashboard tiles may show unfiltered data if the tile was pinned by a user without RLS. RLS is enforced on the underlying dataset, but dashboard tile rendering can vary
Q&A limitations	Q&A (natural language query) respects RLS, but the suggested questions may reference data the user cannot see
Quick Insights	Quick Insights do not respect RLS in all scenarios
Export limitations	Users with export permissions can export filtered data; the export respects RLS
DirectQuery considerations	RLS is evaluated at the Power BI level, not pushed to the source database. For source-level security, use database-level RLS
Publish to Web	RLS is not enforced for Publish to Web embed codes — all data is visible
Email subscriptions	Subscriptions respect the subscriber's RLS; be cautious when subscribing others
XMLA endpoint	Users connecting via XMLA endpoints bypass RLS if they have admin permissions
Paginated reports	RLS is supported when paginated reports use a Power BI dataset as the data source
Composite models	RLS on DirectQuery tables works, but behavior depends on the source

Admin and Member Bypass

This is the most important limitation to understand:

• Users with Admin or Member roles in the workspace always see all data
• This is true even if they are assigned to an RLS role
• Only Contributor and Viewer roles are subject to RLS
• To test RLS for Admin/Member users, use the "Test as role" feature

Publish to Web Warning

When using Publish to Web:

• RLS filters are not applied
• All data in the dataset is accessible through the public URL
• Never use Publish to Web for datasets with sensitive data, even if RLS is configured
• Use direct sharing, apps, or secure embedding instead

---

Troubleshooting RLS

Problem: Users See No Data

Possible Causes:

1. The user's UPN does not match any entry in the security table
2. The security table has a typo in the email address
3. The relationship between the security table and the data tables is incorrect
4. The relationship cross-filter direction is blocking filter propagation

Resolution Steps:

1. Verify the user's UPN by checking Azure AD / Entra ID
2. Confirm the email in the security table matches exactly (check for spaces, case)
3. Verify relationships in the model view
4. Test with "View as role" using the user's exact email address

Problem: Users See All Data

Possible Causes:

1. The user is not assigned to any RLS role in the Service
2. The user has Admin or Member workspace role (bypasses RLS)
3. The RLS filter expression has an error that always returns TRUE
4. The filter is applied to the wrong table

Resolution Steps:

1. Check role assignments in Service (Dataset > Security)
2. Change the user's workspace role to Contributor or Viewer
3. Review the DAX filter expression for logic errors
4. Verify the filter is on the correct table

Problem: Filter Not Propagating

Possible Causes:

1. The relationship between the filtered table and the fact table is missing
2. The cross-filter direction is set to "Single" in the wrong direction
3. There is a broken relationship (mismatched keys)

Resolution Steps:

1. Verify all relationships in the Model view
2. Check that the filter flows from the security table to the fact table
3. If necessary, enable bi-directional filtering on the security relationship
4. Ensure relationship keys match (same data type, same values)

Problem: UPN Mismatch

Possible Causes:

1. The security table uses a different email format than the user's UPN
2. The security table has the user's display name instead of UPN
3. Guest users (B2B) have a modified UPN format (e.g., user_company.com#EXT#@tenant.onmicrosoft.com)

Resolution Steps:

1. Use USERPRINCIPALNAME() in a card visual to display the actual UPN during testing
2. Update the security table to match the exact UPN format
3. For guest users, use their external UPN format or create appropriate mappings

Diagnostic DAX Measure

Create a measure to help debug RLS:

Current User UPN = USERPRINCIPALNAME()

Add this measure to a card visual. When testing with "View as role," it shows the UPN being used for filtering, helping identify mismatches.

Full Troubleshooting Checklist

Check	Action	Expected Result
Role exists	Open Manage Roles in Desktop	Role appears with correct filter
Filter expression valid	Click the checkmark in Manage Roles	No errors
Users assigned to role	Check Security on dataset in Service	User or group listed under the role
Workspace role appropriate	Check workspace access in Service	User is Contributor or Viewer (not Admin/Member)
Security table populated	Review the security table data	User's email has matching entries
UPN format correct	Compare security table entries with actual UPN	Exact match (case-insensitive)
Relationships intact	Review model view	Relationships exist and are active
Filter propagation works	Test with "View as role"	Correct filtered data appears
Multi-role behavior	Assign user to multiple roles and test	User sees union (OR) of permitted data
Performance acceptable	Check report load time with RLS	Acceptable response time

---

Practice Exercises

Exercise 1 — Static RLS Implementation

1. Open a Power BI report with data containing a "Region" or "Category" column
2. Create three static RLS roles, each filtering for a different value
3. In Desktop, use "View as role" to verify each role shows only the expected data
4. Publish to Power BI Service
5. Assign different colleagues (or test accounts) to different roles
6. Have each person verify they see only their assigned data

Exercise 2 — Dynamic RLS with Security Table

1. Create a UserSecurity table with columns: UserEmail, Region
2. Populate it with at least three email addresses and their assigned regions
3. Add the UserSecurity table to your data model
4. Create a relationship between UserSecurity[Region] and your region dimension
5. Create a single RLS role with the filter: [UserEmail] = USERPRINCIPALNAME()
6. Test in Desktop using "View as role" with "Other user" set to each email
7. Publish and assign users in Service
8. Verify that each user sees only their permitted data

Exercise 3 — Manager Hierarchy RLS

1. Create a FlattenedHierarchy table with columns: ManagerEmail, EmployeeEmail
2. Include at least three levels: Director > Manager > Employee
3. Ensure self-referencing rows for each employee
4. Create the relationship to your employee dimension
5. Create an RLS role filtering the hierarchy table by USERPRINCIPALNAME()
6. Test at each level: director sees all subordinates, manager sees their team, employee sees only their own data

Exercise 4 — Testing and Troubleshooting

1. Using the dynamic RLS setup from Exercise 2, intentionally introduce errors: a. Misspell one email address in the security table b. Remove the relationship between the security table and the data table c. Assign a user as a workspace Admin
2. For each error, test the RLS and observe the behavior
3. Document what went wrong and how you identified the issue
4. Fix each error and verify the correct behavior is restored

Exercise 5 — Multi-Tenant RLS

1. Create a dataset representing multi-tenant data (e.g., sales data for three companies: Contoso, Fabrikam, AdventureWorks)
2. Create a TenantSecurity table mapping user emails to TenantID
3. Implement dynamic RLS so each user sees only their company's data
4. Test with "View as role" for each tenant user
5. Consider how you would implement this with CUSTOMDATA() for an embedded scenario (document your approach)

Exercise 6 — OLS Exploration

1. Install Tabular Editor from the External Tools in Power BI Desktop
2. Open your data model in Tabular Editor
3. Create or select an existing RLS role
4. Set one column to None permission (hidden)
5. Save changes back to Power BI Desktop
6. Test with "View as role" and verify the column is not accessible
7. Check what happens to visuals that reference the hidden column

---

Summary

Row-Level Security is one of the most critical features in Power BI for enterprise deployments. In this chapter, you learned:

• RLS restricts data access at the row level, ensuring users see only the data they are authorized to view based on DAX filter expressions applied to tables
• Static RLS uses fixed values in filter expressions, creating a separate role for each access level — simple but difficult to maintain at scale
• Dynamic RLS uses USERPRINCIPALNAME() with a security mapping table to automatically filter data based on the logged-in user — the recommended approach for production
• Hierarchical RLS handles manager-subordinate and geographic hierarchies using flattened mapping tables
• USERPRINCIPALNAME() is the recommended function for identifying users; USERNAME() is a legacy alternative; CUSTOMDATA() is used for embedded scenarios
• Testing RLS is essential — use "View as role" in Desktop and "Test as role" in Service, and always test edge cases
• Filter propagation through relationships is the mechanism that extends RLS filters from security tables to fact tables — proper relationship design is critical
• Object-Level Security (OLS) complements RLS by hiding columns or tables from specific roles, configured through Tabular Editor
• Best practices include using dynamic RLS, maintaining a security table, minimizing bi-directional relationships, keeping filter expressions simple, and testing after every change
• Common patterns include regional access, department-based access, manager hierarchy, multi-tenant security, and full access roles
• Key limitations include admin/member bypass, no RLS on Publish to Web, and DirectQuery considerations
• Troubleshooting focuses on UPN mismatches, missing role assignments, broken relationships, and incorrect filter propagation

With RLS implemented, your reports are secure and personalized. In the next chapter, you will learn how to optimize the performance of your Power BI reports and data models.