Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
This article targets you as a data modeler working with Power BI Desktop. It describes good design practices for enforcing row-levels security (RLS) in your data models.
It's important to understand RLS filters table rows. They can't be configured to restrict access to model objects, including tables, columns, or measures.
Note
This article doesn't describe RLS or how to set it up. For more information, see Restrict data access with row-level security (RLS) for Power BI Desktop.
Also, it doesn't cover enforcing RLS in live connections to external-hosted models with Azure Analysis Services or SQL Server Analysis Services. In these cases, RLS is enforced by Analysis Services. When Power BI connects using single-sign on (SSO), Analysis Services will enforce RLS (unless the account has admin privileges).
It's possible to create multiple roles. When you're considering the permission needs for a single report user, strive to create a single role that grants all those permissions, instead of a design where a report user will be a member of multiple roles. It's because a report user could map to multiple roles, either directly by using their user account or indirectly by security group membership. Multiple role mappings can result in unexpected outcomes.
When a report user is assigned to multiple roles, RLS filters become additive. It means report users can see table rows that represent the union of those filters. What's more, in some scenarios it's not possible to guarantee that a report user doesn't see rows in a table. So, unlike permissions applied to SQL Server database objects (and other permission models), the "once denied always denied" principle doesn't apply.
Consider a model with two roles: The first role, named Workers, restricts access to all Payroll table rows by using the following rule expression:
FALSE()
Note
A rule will return no table rows when its expression evaluates to FALSE
.
Yet, a second role, named Managers, allows access to all Payroll table rows by using the following rule expression:
TRUE()
Take care: Should a report user map to both roles, they'll see all Payroll table rows.
RLS works by automatically applying filters to every DAX query, and these filters may have a negative impact on query performance. So, efficient RLS comes down to good model design. It's important to follow model design guidance, as discussed in the following articles:
In general, it's often more efficient to enforce RLS filters on dimension-type tables, and not fact-type tables. And, rely on well-designed relationships to ensure RLS filters propagate to other model tables. RLS filters only propagate through active relationships. So, avoid using the LOOKUPVALUE DAX function when model relationships could achieve the same result.
Whenever RLS filters are enforced on DirectQuery tables and there are relationships to other DirectQuery tables, be sure to optimize the source database. It can involve designing appropriate indexes or using persisted computed columns. For more information, see DirectQuery model guidance in Power BI Desktop.
It's possible to measure the performance impact of RLS filters in Power BI Desktop by using Performance Analyzer. First, determine report visual query durations when RLS isn't enforced. Then, use the View As command on the Modeling ribbon tab to enforce RLS and determine and compare query durations.
Once published to Power BI, you must map members to semantic model roles. Only semantic model owners or workspace admins can add members to roles. For more information, see Row-level security (RLS) with Power BI (Manage security on your model).
Members can be user accounts, security groups, distribution groups or mail enabled groups. Whenever possible, we recommend you map security groups to semantic model roles. It involves managing security group memberships in Microsoft Entra ID. Possibly, it delegates the task to your network administrators.
Test each role to ensure it filters the model correctly. It's easily done by using the View As command on the Modeling ribbon tab.
When the model has dynamic rules using the USERNAME DAX function, be sure to test for expected and unexpected values. When embedding Power BI content—specifically using the embed for your customers scenario—app logic can pass any value as an effective identity user name. Whenever possible, ensure accidental or malicious values result in filters that return no rows.
Consider an example using Power BI embedded, where the app passes the user's job role as the effective user name: It's either "Manager" or "Worker". Managers can see all rows, but workers can only see rows where the Type column value is "Internal".
The following rule expression is defined:
IF(
USERNAME() = "Worker",
[Type] = "Internal",
TRUE()
)
The problem with this rule expression is that all values, except "Worker", return all table rows. So, an accidental value, like "Wrker", unintentionally returns all table rows. Therefore, it's safer to write an expression that tests for each expected value. In the following improved rule expression, an unexpected value results in the table returning no rows.
IF(
USERNAME() = "Worker",
[Type] = "Internal",
IF(
USERNAME() = "Manager",
TRUE(),
FALSE()
)
)
Sometimes, calculations need values that aren't constrained by RLS filters. For example, a report may need to display a ratio of revenue earned for the report user's sales region over all revenue earned.
While it's not possible for a DAX expression to override RLS—in fact, it can't even determine that RLS is enforced—you can use a summary model table. The summary model table is queried to retrieve revenue for "all regions" and it's not constrained by any RLS filters.
Let's see how you could implement this design requirement. First, consider the following model design:
The model comprises four tables:
The following expression defines the SalesRevenueSummary calculated table:
SalesRevenueSummary =
SUMMARIZECOLUMNS(
Sales[OrderDate],
"RevenueAllRegion", SUM(Sales[Revenue])
)
Note
An aggregation table could achieve the same design requirement.
The following RLS rule is applied to the Salesperson table:
[EmailAddress] = USERNAME()
Each of the three model relationships is described in the following table:
Relationship | Description |
---|---|
There's a many-to-many relationship between the Salesperson and Sales tables. The RLS rule filters the EmailAddress column of the hidden Salesperson table by using the USERNAME DAX function. The Region column value (for the report user) propagates to the Sales table. | |
There's a one-to-many relationship between the Date and Sales tables. | |
There's a one-to-many relationship between the Date and SalesRevenueSummary tables. |
The following expression defines the Revenue % All Region measure:
Revenue % All Region =
DIVIDE(
SUM(Sales[Revenue]),
SUM(SalesRevenueSummary[RevenueAllRegion])
)
Note
Take care to avoid disclosing sensitive facts. If there are only two regions in this example, then it would be possible for a report user to calculate revenue for the other region.
Sometimes it makes sense to avoid using RLS. If you have only a few simplistic RLS rules that apply static filters, consider publishing multiple semantic models instead. None of the semantic models define roles because each semantic model contains data for a specific report user audience, which has the same data permissions. Then, create one workspace per audience and assign access permissions to the workspace or app.
For example, a company that has just two sales regions decides to publish a semantic model for each sales region to different workspaces. The semantic models don't enforce RLS. They do, however, use query parameters to filter source data. This way, the same model is published to each workspace—they just have different semantic model parameter values. Salespeople are assigned access to just one of the workspaces (or published apps).
There are several advantages associated with avoiding RLS:
However, there are disadvantages associated with avoiding RLS:
If RLS produces unexpected results, check for the following issues:
When a specific user can't see any data, it could be because their UPN isn't stored or it's entered incorrectly. It can happen abruptly because their user account has changed as the result of a name change.
Tip
For testing purposes, add a measure that returns the USERNAME DAX function. You might name it something like "Who Am I". Then, add the measure to a card visual in a report and publish it to Power BI.
Creators and consumers with only Read permission on the semantic model will only be able to view the data they're allowed to see (based on their RLS role mapping).
When a user views a report in either a workspace or an app, RLS might or might not be enforced depending on their semantic model permissions. For this reason, it's critical that content consumers and creators only possess Read permission on the underlying semantic model when RLS must be enforced. For details about the permissions rules that determine whether RLS is enforced, see the Report consumer security planning article.
For more information related to this article, check out the following resources:
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayTraining
Module
Implement row-level security - Training
Row-level security (RLS) allows you to create a single or a set of reports that targets data for a specific user. In this module, you'll learn how to implement RLS by using either a static or dynamic method and how Microsoft Power BI simplifies testing RLS in Power BI Desktop and Power BI service.
Certification
Microsoft Certified: Power BI Data Analyst Associate - Certifications
Demonstrate methods and best practices that align with business and technical requirements for modeling, visualizing, and analyzing data with Microsoft Power BI.