Managing data access is a critical part of using Power BI effectively, especially when dealing with sensitive information. In Power BI, two primary methods are often used to control data access: Row-Level Security (RLS) and Object-Level Security (OLS). While each of these features is powerful on its own, it is important to note that using them together is not possible and will lead to errors. Understanding their differences, limitations, and use cases will help you make informed decisions on how to secure your data in Power BI.

TL;DR: configure RLS to work like OLS

There is no perfect solution when it comes to Power BI RLS and OLS. They just don’t work together. You’ll have to pick a side and will most likely end up picking RLS, because OLS will be too limited. However, there is a way to configure RLS to act like OLS, albeit without filtering the entire column or table from the model. Users with access to sensitive information will be able to view that information in the related columns. Users without access will still see the column, but it will be empty. As long as users don’t have direct access to the dataset, they will never be able to access the data.

Whether this solution fits your exact needs is up to you. If so, this article is for you!

About Row Level Security

Row-Level Security allows organizations to control which rows of data a user can see based on their assigned roles. It is implemented by creating security roles and using DAX (Data Analysis Expressions) filters to restrict data access to certain users or groups. For example, if you manage sales data for multiple regions, RLS can ensure that a sales manager for Region A only sees data for that specific region.

Pros of RLS

• Granular Data Control: RLS offers precise control over which rows of data a user can view, ensuring that users only see the data relevant to their role.
• Easy Role Setup: Power BI provides a relatively simple interface for setting up roles and assigning filters based on user attributes.
• Supports Dynamic Filters: RLS can dynamically filter data based on the logged-in user, making it easier to handle multiple users with varying access levels.

Cons of RLS

• Complex Role Management: As the number of roles and filters increases, managing RLS can become cumbersome, especially in large organizations with diverse user needs.
• Potential Performance Issues: With large datasets and complex filtering logic, applying RLS can slow down report performance as more data needs to be processed.
• No Support for Object-Level Restrictions: RLS does not restrict access to entire tables or columns—only the rows within the data.

About Object Level Security

Object-Level Security goes beyond restricting data visibility by limiting access to specific tables or columns. OLS is used to hide sensitive objects, like tables containing confidential information (e.g., employee salaries), so that they are not even visible in the data model to unauthorized users.

Pros of OLS

• Complete Data Hiding: OLS provides full control over who can see specific tables or columns, ensuring that sensitive data is completely hidden from unauthorized users.
• Simplified Data Views: By hiding unnecessary or sensitive data, OLS can simplify the data model and improve the user experience for report viewers.
• Protection at the Model Level: OLS prevents unauthorized access to hidden objects, which helps safeguard against accidental data leaks.

Cons of OLS

• Limited to Objects: Unlike RLS, OLS cannot filter rows of data within a table—it can only hide entire tables or columns.
• Static Nature: OLS works on predefined roles, so it lacks flexibility for scenarios where users need variable access to data based on real-time conditions.
• Complex Setup: Setting up OLS can be more challenging and requires detailed knowledge of the data model and security requirements.

Why RLS and OLS cannot be used together

While both RLS and OLS serve important functions in controlling data access, they cannot be used together in Power BI. Combining RLS and OLS results in unexpected errors and inconsistencies in how security rules are applied. This is because Power BI handles these two security mechanisms separately, and attempting to combine them can cause conflicts in how data visibility is managed. For example, applying RLS to a dataset with hidden tables or columns (using OLS) most likely leads to errors where certain data is not correctly filtered or is inadvertently exposed. Even more; trying to combine OLS and RLS in a datamodel will simply result in a Power BI Error. Not at the moment of modelling (Power BI will act if its nose is bleeding), but the moment you assign both a role containing RLS and a role containing OLS, it will throw you an error. This can be witnessed below.

This incompatibility is a key limitation when designing a security model in Power BI. It forces organizations to carefully choose between the two depending on their data security needs. If your primary goal is to restrict access to specific rows of data, RLS is the go-to option. If you need to hide entire tables or fields, OLS is more appropriate. However, trying to do both will likely result in complications.

But luckily, we have a solution where we can model RLS in a way that it acts like OLS. The next paragraph will explain how to achieve this result and not force your organization to choose the one or the other.

Building the solution

Follow follow these steps to achieve hiding sensitive data in Power BI via Row Level Security:

1. Split your table with sensitive information into a non-sensitive table and a sensitive table. Most likely you will do this in your dataplatform, but Power Query will suffice too
2. Add a column to the sensitive table, name it SensitivityLabel and insert the number 1. We will use this to filter the datamodel via RLS
3. Create a relationship between the two tables based on the Primary Key (PK)
4. Hide the table completely containing the sensitive data. This will hide the entire table in the front-end for users. You will end up with a datamodel like shown below

5. Move to the report page in your report
6. We’re first going to create our RLS-roles. Open Manage roles via the Modeling tab and create two roles: Has_Access and No_Access. Both roles filter the Dim_Participant_Sensitive table, but the Has_Access role filters on the column SensitivityLabel for a value equals 1. The No_Access filters this column for a value equals 0. This setup can be witnessed below.

5. Now that we prepared our data and our security, we can introduce some DAX to get our solution going. Go ahead and create a measure in your non-sensitive table (or in your measure table, whatever you prefer). Here you will need the following DAX expression:

BSN Measure = 
CALCULATETABLE(
    VALUES(Dim_Participant_Sensitive[BSN]),
    CROSSFILTER(Dim_Participant[PK],Dim_Participant_Sensitive[PK],Both)
)

What this expression does is that it takes the sensitive values from the column in the sensitive table and applies a crossfilter on the table without sensitive information. When modeled this way, you can select a single table from the Power BI visualizations containing all necessary information for your users but will prevent filtering the entire table when a user does not have access rights to the sensitive information. The result is shown below.

But a user with the appropriate rights via the RLS-role Has_Access will view the table as shown below. The BSNs are visible.

If you don’t use [CROSSFILTER], you will end up with a table as shown below. It will be completely filtered for users with unsufficient access rights and will be considered as a bad user experience.

Conclusion

RLS and OLS are not meant to be used happily ever after. But you can combine the best of both worlds by using the solution discussed in this article. Of course, when your datamodel grows, the solution becomes more complex. You could automate most of it via a combination of DBT, Databricks, Microsoft Entra and the Power BI XMLA-endpoint. More articles will follow up on this topic to help you build an end-to-end solution your organization can rely on.