This chapter covers Row-Level Security (RLS) in Power BI, a critical feature for restricting data access to specific users based on their identity or role. For the DP-900 exam, RLS falls under objective 3.4: 'Implement and manage a data analytics solution', and typically appears in 5-10% of exam questions. Understanding RLS is essential for ensuring data security and compliance in enterprise reporting environments, and the exam tests both conceptual understanding and practical configuration steps.
Jump to a section
Row-Level Security (RLS) in Power BI works like a corporate building with a security badge system that grants different access to different employees based on their role. Imagine a building with multiple floors, each containing different departments. The security system has a database that maps each employee's badge ID to the floors and rooms they are allowed to enter. When an employee swipes their badge at a door, the system checks the database and only unlocks the door if the employee is authorized for that specific area. Similarly, in Power BI, RLS uses roles and rules defined in the data model. A role is like a badge type (e.g., 'Manager' or 'Sales Rep'), and the rule is the access map (e.g., 'only see data for your region'). When a user opens a Power BI report, the system checks their identity (like a badge swipe) and applies the RLS filters to the data, showing only the rows they are permitted to see. Just as a badge does not grant access to areas not mapped, RLS ensures a user never sees data outside their defined scope. Importantly, RLS filters are applied at the data source query level, not after the data is loaded, ensuring security is enforced before any data reaches the report. This is analogous to the door lock physically preventing entry, rather than just asking employees not to go into certain rooms.
What is Row-Level Security (RLS) and Why It Exists
Row-Level Security (RLS) is a feature in Power BI that allows you to control access to data at the row level within a dataset. This means different users see different subsets of data when viewing the same report or dashboard. For example, a sales manager in North America should only see sales data for their region, while a manager in Europe sees only European data. RLS enforces these restrictions automatically based on the user's identity, without requiring separate reports for each user group.
RLS is essential for organizations that need to share data broadly but must comply with data privacy regulations (e.g., GDPR, HIPAA) or internal policies that restrict access to sensitive information. Without RLS, you would need to create multiple datasets or reports for each user group, leading to duplication and maintenance overhead.
How RLS Works Internally
RLS operates by applying filter predicates to the queries sent to the data source. When a user opens a Power BI report, Power BI identifies the user (via their UPN or a custom username mapping) and then evaluates the RLS roles assigned to that user. For each role, a DAX (Data Analysis Expressions) filter expression is defined. This expression is a logical condition that returns TRUE for rows the user is allowed to see. Power BI modifies the query to include this filter, effectively adding a WHERE clause to the underlying SQL or M query.
For example, consider a table called 'Sales' with columns 'Region', 'SalesAmount', and 'SalesPerson'. A role named 'NorthAmerica' might have the filter [Region] = "North America". When a user assigned to this role opens a report, Power BI transforms every query against the 'Sales' table to include WHERE Region = 'North America'. This happens at the data source level, so the user never even sees that other regions exist.
RLS is applied to all visuals, pages, and even the underlying data model. It works with both Import and DirectQuery modes, though there are nuances. In Import mode, the filter is applied after data is loaded into memory, but still before any visual renders. In DirectQuery mode, the filter is passed directly to the source query.
Key Components and Configuration
To configure RLS, you need:
Roles: Named sets of permissions (e.g., 'Manager', 'Employee'). Roles are defined in Power BI Desktop under the 'Manage Roles' option.
Filters: DAX expressions that evaluate to TRUE or FALSE. These filters are applied row-by-row. Only rows where the expression evaluates to TRUE are visible.
User Mapping: After publishing to the Power BI Service, you assign users or groups to roles using the 'Security' option in the dataset settings.
Important defaults and limitations:
RLS is not applied to the model in Power BI Desktop; it only takes effect after publishing to the Power BI Service. In Desktop, you can test RLS using the 'View as Roles' option.
RLS filters are static at design time; they cannot dynamically evaluate based on runtime user input (e.g., a slicer selection) unless you use dynamic RLS with USERNAME() or USERPRINCIPALNAME() functions.
RLS works with Azure Analysis Services and SQL Server Analysis Services as well, but the configuration differs slightly.
RLS does not apply to the underlying data source if the dataset uses DirectQuery with single sign-on (SSO) – in that case, security must be enforced at the source.
Configuration Steps
Define roles in Power BI Desktop: Go to the 'Modeling' tab, click 'Manage Roles'. Create a new role and add tables. For each table, write a DAX filter expression. Example: = [Region] = "North America".
Test roles: Use 'View as Roles' in the Modeling tab to see how the report looks for a specific role.
Publish to Power BI Service: Publish the report and dataset.
Assign users: In the Power BI Service, navigate to the dataset, click 'Security', and add users or groups to the roles. Users must have at least Viewer permissions on the report or workspace.
Verify: Users will see only the data allowed by their role. If a user belongs to multiple roles, they see the union of data from all roles.
RLS in DirectQuery and Live Connection
DirectQuery: RLS filters are sent as part of the SQL query to the source. The source must support the filter expressions. Performance depends on the source's ability to handle the filters.
Live Connection: If the dataset is connected to an Azure Analysis Services or SQL Server Analysis Services model, RLS must be defined in that model, not in Power BI. Power BI respects the RLS already configured in the source.
Dynamic RLS
Dynamic RLS uses the USERNAME() or USERPRINCIPALNAME() DAX functions to determine which rows a user can see based on a mapping table. For example, create a table 'UserRegion' that maps each user's email to a region. Then define a role filter like = [Region] = LOOKUPVALUE(UserRegion[Region], UserRegion[Email], USERPRINCIPALNAME()). This allows you to manage access via a data table rather than modifying roles for each user.
RLS and Row-Level Security in Azure Synapse
Azure Synapse Analytics also supports row-level security using the CREATE SECURITY POLICY command in T-SQL. This is different from Power BI RLS but can be used together. If the Power BI dataset uses DirectQuery against Synapse, you can rely on the source's RLS instead of Power BI's, which can be more performant for large datasets.
Performance Considerations
RLS adds overhead to queries because every query must include the filter condition. For large datasets, this can slow down report rendering.
Use static RLS when possible; dynamic RLS with LOOKUPVALUE can be slower.
In Import mode, RLS filters are applied in memory, which is generally fast but consumes memory for the entire dataset.
In DirectQuery, ensure the source has appropriate indexes on the filtered columns.
RLS filters are applied to every query, including those for visuals, so avoid overly complex DAX expressions.
Common Pitfalls
Forgetting to assign users to roles after publishing – users will see all data (no restriction) or an error if the role is empty.
Using RLS with calculated tables or measures: RLS does not filter calculated tables; it filters only base tables. Measures are evaluated in the context of the filter, so they respect RLS.
RLS does not apply to the 'Analyze in Excel' feature unless the user is assigned a role.
RLS is not applied to the underlying dataset when using the XMLA endpoint – all rows are accessible via external tools.
RLS and Power BI Embedded
In Power BI Embedded, RLS roles can be passed programmatically using the embed token. The token includes the roles and the username, so the embedded report respects the same security. This is crucial for multi-tenant applications where each customer sees only their data.
Identify Data Sensitivity Requirements
Before implementing RLS, determine which data needs to be restricted and who should have access. For example, sales data may need to be filtered by region, and HR data by department. Document the mapping of users to allowed data. This step is critical because RLS is only as good as the rules you define. In the DP-900 exam, you may be asked to identify scenarios where RLS is appropriate, such as when different managers should see only their team's performance.
Define RLS Roles in Power BI Desktop
Open Power BI Desktop, go to the Modeling tab, and click 'Manage Roles'. Create a new role with a descriptive name like 'SalesManager'. Select the table you want to filter, and write a DAX expression that returns TRUE for rows the role should see. For example: `= [Region] = "North America"`. You can use multiple conditions with AND/OR. Test the role using 'View as Roles'. In the exam, know that roles are defined in Desktop but only enforced after publishing.
Publish Dataset to Power BI Service
Publish the report and dataset to a workspace in the Power BI Service. Ensure the dataset is in a workspace that supports RLS (all workspaces do). After publishing, the roles you defined are available in the dataset settings. The exam may test that RLS is not active in Desktop; it only works in the service.
Assign Users to Roles in Power BI Service
In the Power BI Service, navigate to the dataset, click 'Security', and add users or security groups to the appropriate roles. Users must have at least Viewer permissions on the report or workspace. If a user is not assigned to any role, they will see all data (if the dataset has no RLS) or no data (if RLS is enabled but no roles assigned). The exam might ask about the permission level required: 'Viewer' or higher.
Verify RLS Enforcement with Test Users
After assigning users, have a test user log in and view the report. They should see only the data allowed by their role. You can also use the 'View as Roles' feature in the service (available in the dataset settings) to simulate a user. The exam may ask how to test RLS: use 'View as Roles' in Desktop or the service. Note that RLS does not apply to the report author or workspace admins; they see all data.
Enterprise Scenario 1: Multi-Region Sales Reporting
A global retail company with sales teams in North America, Europe, and Asia needs to share a single Power BI dashboard showing sales performance. Each regional manager should see only their region's data. The company uses RLS with a static role per region. The filter expression for the 'Europe' role is = [Region] = "Europe". The dataset is in Import mode and refreshed daily. Performance is acceptable because the dataset is under 10 million rows. However, when the company tried to use dynamic RLS with a lookup table mapping users to regions, they encountered slow report load times because the LOOKUPVALUE function was evaluated for every row. They switched to static roles, which improved performance. Common issues include forgetting to assign new hires to roles, leading to them seeing no data or all data depending on configuration.
Enterprise Scenario 2: Healthcare Data Compliance
A hospital system uses Power BI to analyze patient outcomes. Due to HIPAA, each doctor should see only their own patients. They implement dynamic RLS using a 'PatientAccess' table with columns 'DoctorEmail' and 'PatientID'. The role filter is = [PatientID] IN VALUES(PatientAccess[PatientID]) and USERPRINCIPALNAME() = SELECTEDVALUE(PatientAccess[DoctorEmail]). This ensures that when a doctor views the report, only their patients appear. The dataset uses DirectQuery to an Azure SQL Database, which has its own row-level security. The Power BI RLS is redundant but provides an additional layer. Performance is critical: the DirectQuery must be optimized with indexes on PatientID. Misconfiguration here could lead to data breaches, so they regularly audit permissions.
Enterprise Scenario 3: Multi-Tenant SaaS Application
A SaaS company provides analytics to its customers using Power BI Embedded. Each customer (tenant) should see only their own data. They use RLS with a single role 'TenantUser' and a dynamic filter: = [TenantID] = LOOKUPVALUE(TenantMapping[TenantID], TenantMapping[UserEmail], USERPRINCIPALNAME()). When embedding reports, they generate an embed token that includes the username and role. This scales to hundreds of tenants. A common mistake is not passing the correct username in the embed token, causing users to see all data. They also use Azure AD groups to manage role assignments, though dynamic RLS reduces the need for individual assignments.
What DP-900 Tests on RLS
DP-900 objective 3.4 expects you to understand the purpose of RLS, how to configure it, and its limitations. Specific exam topics include:
Identifying when RLS is needed: Questions may present a scenario (e.g., 'A company wants managers to see only their team's data') and ask which feature to use. The correct answer is RLS.
Understanding that RLS is configured in Power BI Desktop but enforced in the service: A common trick is to ask where RLS is applied. Many candidates choose 'Power BI Desktop' because that's where roles are created, but enforcement happens only after publishing.
Knowing that RLS uses DAX filter expressions: The exam may ask which language is used for RLS filters – DAX, not M or SQL.
Recognizing that RLS does not apply to the model author or workspace admins: A question might say 'A user creates a report with RLS and publishes it. When they view the report, they see all data. Why?' The answer: The author is exempt from RLS.
Dynamic RLS with USERPRINCIPALNAME(): You may be asked how to create a role that automatically filters based on the logged-in user.
Common Wrong Answers and Why Candidates Choose Them
'RLS is applied in Power BI Desktop' – Candidates confuse where roles are defined versus where they are enforced. The definition is in Desktop; enforcement is in the service.
'RLS is configured using SQL filters' – Power BI uses DAX, not SQL, for RLS filters. However, if the dataset uses DirectQuery, the filter may be translated to SQL, but the definition is DAX.
'RLS requires each user to have a separate role' – Static roles can be shared by multiple users. Dynamic RLS can handle many users with one role.
'RLS filters are applied to the entire dataset, including calculated tables' – RLS filters do not apply to calculated tables; they only filter base tables. Measures respect RLS because they are evaluated in the filter context.
Numbers, Values, and Terms That Appear Verbatim
USERPRINCIPALNAME() – the DAX function to get the current user's UPN.
'Manage Roles' – the button in the Modeling tab.
'View as Roles' – the testing feature.
'Security' – the menu option in the dataset settings for assigning users.
'DAX' – the language for RLS expressions.
Edge Cases and Exceptions
If a user is assigned to multiple roles, they see the union of all role filters (OR logic).
RLS does not apply to the underlying data source if the dataset uses DirectQuery with SSO; security must be at the source.
RLS does not apply to the 'Analyze in Excel' feature unless the user's credentials are passed.
RLS is not supported for live connections to Power BI datasets (you must define RLS in the source dataset).
How to Eliminate Wrong Answers
If the question asks about 'restricting data at the row level', eliminate answers about column-level security (which is not a built-in Power BI feature) or object-level security.
If the question mentions 'dynamic security based on the logged-in user', look for USERPRINCIPALNAME() or USERNAME().
If the question asks where to assign users to roles, the answer is 'Power BI Service', not Desktop.
RLS restricts data access at the row level based on the user's identity or role.
RLS roles are defined in Power BI Desktop using DAX filter expressions.
RLS is enforced only after the dataset is published to the Power BI Service.
Users must be assigned to roles in the Power BI Service dataset security settings.
A user assigned to multiple roles sees the union (OR) of data from all roles.
Dynamic RLS uses USERPRINCIPALNAME() or USERNAME() to filter based on the logged-in user.
RLS does not apply to the report author or workspace admins; they see all data.
RLS does not filter calculated tables; it only filters base tables.
In DirectQuery mode, RLS filters are passed to the source query.
Testing RLS can be done using 'View as Roles' in Power BI Desktop or the service.
These come up on the exam all the time. Here's how to tell them apart.
Static RLS
Roles have fixed filter expressions, e.g., [Region] = 'North America'.
You need a separate role for each distinct filter value (e.g., one per region).
User assignment is done manually in the Power BI Service for each role.
Performance is generally better because the filter is a simple constant comparison.
Less flexible; adding a new region requires creating a new role and assigning users.
Dynamic RLS
Filter expression uses DAX functions like USERPRINCIPALNAME() to determine the user's allowed data at runtime.
One role can serve many users by referencing a mapping table (e.g., UserRegion).
User assignment is managed via the mapping table; no need to assign users to roles individually.
Performance may be slower due to LOOKUPVALUE or other dynamic functions evaluated per row.
Highly flexible; adding a new user only requires adding a row to the mapping table.
Mistake
RLS is enforced in Power BI Desktop as soon as you define roles.
Correct
RLS is only enforced after the dataset is published to the Power BI Service. In Desktop, you can test roles using 'View as Roles', but the actual security is not active. The author always sees all data in Desktop.
Mistake
RLS filters apply to all tables, including calculated tables.
Correct
RLS filters only apply to base tables (imported or DirectQuery). Calculated tables are evaluated once and are not filtered by RLS. Measures, however, respect RLS because they are evaluated in the filter context of the visual.
Mistake
You must create a separate role for each user.
Correct
Roles are typically defined for groups of users (e.g., 'Sales Managers'). You can assign multiple users to the same role. For individual user-specific filters, use dynamic RLS with USERPRINCIPALNAME() and a mapping table.
Mistake
RLS can be applied to the report page level or visual level.
Correct
RLS applies to the entire dataset. It filters rows globally, affecting all visuals and pages. You cannot use RLS to restrict a specific visual while leaving others unrestricted; that would require separate datasets or reports.
Mistake
RLS works the same way for DirectQuery and Import mode.
Correct
In Import mode, RLS filters are applied in memory after data is loaded. In DirectQuery, the filter is sent as part of the SQL query to the source. Additionally, if DirectQuery uses single sign-on (SSO), the source's own security (e.g., SQL Server RLS) may be used instead of Power BI RLS.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
RLS is a feature that restricts data access at the row level based on user identity. For example, a sales manager in Europe sees only European sales data. It uses DAX filter expressions defined in roles, which are enforced after the dataset is published to Power BI Service.
First, in Power BI Desktop, go to Modeling > Manage Roles, create a role, and write a DAX filter expression (e.g., [Region] = 'Europe'). Then publish the report to Power BI Service. In the service, go to dataset settings > Security, and add users to the role.
RLS is not enforced in Power BI Desktop. You can test it using 'View as Roles', but the actual security only applies after publishing to the service. The report author always sees all data in Desktop.
Dynamic RLS uses DAX functions like USERPRINCIPALNAME() to determine which rows a user can see based on a mapping table, without needing separate roles for each user. Static RLS uses fixed filter expressions and requires separate roles for each filter value. Dynamic RLS is more flexible but can be slower.
Yes, RLS works with DirectQuery. The filter expressions are sent as part of the SQL query to the source. However, if the dataset uses single sign-on (SSO), you may rely on the source's own row-level security instead.
The user sees the union of data from all roles they belong to. For example, if one role allows 'Region = North America' and another allows 'Region = Europe', the user sees data from both regions.
No, the report author and workspace admins are exempt from RLS. They always see all data. This is important for testing and administration.
You've just covered Row-Level Security in Power BI — now see how well it sticks with free DP-900 practice questions. Full explanations included, no account needed.
Done with this chapter?