Implementing Row-Level Security in Power BI

Row-Level Security (RLS) ensures users only see data they are authorized to access. For enterprises handling sensitive data in healthcare, finance, and government, RLS is not optional—it is a compliance requirement. Organizations using RLS effectively reduce report sprawl by 70% while meeting HIPAA, GDPR, and SOC 2 data access controls. Our Power BI consulting team can help you implement RLS correctly from day one.

What is Row-Level Security?

RLS restricts data access at the row level based on user identity. Unlike workspace-level permissions that control who can open a report, RLS controls which rows of data each user sees within the same report. Two users viewing the identical dashboard see completely different numbers based on their authorization level.

Key Benefits:

| Benefit | Without RLS | With RLS | |---|---|---| | Report distribution | Separate report per group | Single report serves all users | | Data protection | Manual filtering (error-prone) | Engine-enforced row filtering | | Compliance auditing | Difficult to prove access control | Built-in audit trail via activity logs | | Maintenance effort | N reports x N updates | One report, one update | | Export safety | Users can export all data | Exports respect RLS filters |

RLS is enforced at the Analysis Services engine level. Users cannot bypass it through DAX queries, Analyze in Excel, API access, or data exports. This makes it suitable for regulatory compliance where access controls must be provably enforced.

Static vs Dynamic RLS

Static RLS

Hardcoded filter values for specific roles: - Sales East Role: `[Region] = "East"` - Sales West Role: `[Region] = "West"` - Best for simple scenarios with fewer than 10 roles - Requires creating a new role for every new region, department, or entity - Becomes unmanageable at scale—50 regions means 50 roles to maintain

Dynamic RLS

Filter based on user identity using DAX functions: - `USERPRINCIPALNAME()` returns the authenticated user's email (e.g., [email protected]) - A single role definition handles all users by looking up their permissions in a security mapping table - Scales to thousands of users without creating thousands of roles - Access changes take effect on next dataset refresh—no role editing required

When to use each: - Static RLS: Fewer than 10 fixed groups, simple filtering, proof-of-concept environments - Dynamic RLS: Production deployments, 10+ user groups, self-maintaining security, compliance requirements

Implementation Steps

Step 1: Design Your Security Model

Create a security mapping table that links user identities to their authorized data scope. This table drives all dynamic RLS filtering:

• UserPrincipalName column matching Azure AD email addresses exactly (case-insensitive in DAX but match the format)
• Data scope columns for each dimension being secured (Region, Department, CostCenter, CustomerID)
• Wildcard rows for admin users who should see all data (use "*" and handle in DAX)
• Multiple rows per user when a user has access to multiple regions or departments

Store this table in a database, SharePoint list, or Excel file that refreshes with the dataset. Changes to user access propagate automatically on next refresh without modifying role definitions.

Step 2: Build Relationships

Connect your security table to the data model following star schema design principles. The security table relates to dimension tables (not directly to fact tables), and the star schema relationships propagate filters to fact tables automatically.

Critical: Set the relationship cross-filter direction to single (from security table to dimension table). Bi-directional filtering on security relationships can create ambiguous filter paths and unintended data exposure.

Step 3: Create Roles in Power BI Desktop

1. Go to Modeling > Manage Roles
2. Create a new role (e.g., "DynamicAccess")
3. Add a DAX filter expression on the security table: `[UserPrincipalName] = USERPRINCIPALNAME() || [UserPrincipalName] = "*"`
4. The wildcard check (`"*"`) allows admin rows to bypass filtering
5. Save the role—this single role handles all users

Step 4: Test with "View as Role"

Before publishing, test thoroughly in Power BI Desktop: - Use View as Role (Modeling tab) and enter specific user emails - Verify that each simulated user sees only their authorized data - Test edge cases: new employees not yet in security table (should see no data), users with multiple region assignments, admin wildcard users - Compare totals between the unfiltered view and the RLS-filtered view to confirm no data leakage

Step 5: Assign Users in Power BI Service

After publishing to the Power BI Service: 1. Open the dataset settings 2. Navigate to Security tab 3. Assign Azure AD users or security groups to the DynamicAccess role 4. Azure AD groups are strongly recommended—add/remove users from the group instead of editing dataset security 5. Test in the Service using "Test as role" with specific user accounts

Step 6: Monitor and Audit

Use Power BI audit logs and the Admin API to monitor RLS in production: - Track which users accessed which reports and when - Monitor RLS filter execution in refresh and query logs - Detect failed access attempts or unusual data access patterns - Generate compliance reports showing data access controls are enforced - Set up alerts for users added to high-privilege admin wildcard rows

Performance Considerations

RLS adds query overhead because the engine must evaluate the DAX filter expression for every query. Minimize impact with these practices:

• Filter dimension tables, not fact tables—let star schema relationships propagate the filter
• Keep the security table small—one row per user-scope combination. For 1,000 users with 5 regions each, that is 5,000 rows maximum
• Index the security lookup if the security table is in a database (index on UserPrincipalName)
• Avoid complex DAX in RLS expressions—LOOKUPVALUE and simple equality checks are fast; avoid iterating over large tables
• Test with realistic concurrency—RLS performance can degrade with hundreds of concurrent users on undersized capacity

Common Pitfalls to Avoid

• Not testing all user personas before production—test at least one user per role/region combination
• Forgetting bi-directional filter implications—bi-directional relationships can allow data to flow in unexpected directions through the security table
• Complex DAX in filter expressions that evaluates slowly—keep RLS DAX as simple as possible
• Missing the security table refresh—if the security table does not refresh when users are added, new employees see no data
• Relying on RLS alone without workspace-level permissions—defense in depth requires both

RLS in Microsoft Fabric

Microsoft Fabric extends RLS capabilities with: - **Object-Level Security (OLS)** for hiding entire columns or tables from specific roles - **Direct Lake RLS support so real-time data access remains secure without import refresh - Unified governance across all Fabric workloads with consistent security policies - Sensitivity labels** that integrate with Microsoft Purview for data classification alongside RLS

Need help implementing RLS? Contact us for a security assessment.