How and When to Use Power BI Row-Level Security

Row-level security is a powerful data governance capability of Power BI and should be learned by anyone looking to protect sensitive data. In this blog post, I provide a high-level summary of row-level security, why it is important, when to use it, and how to set it up within Power BI.

What is Row-Level Security?

Row-level security (RLS) is a way to limit the data an end-user has visibility into within a Power BI report. RLS is a horizontal limitation applied to rows within a table, whereas Object-level security (OLS) is a vertical limitation that restricts column-level and table-level visibility. For limitation to occur, you will need to feed Power BI instructions outlining the data to be filtered and the users for which visibility should be limited. 

Why is Row-Level Security Important?

RLS is a key component of an organization’s data protection strategy. By implementing RLS, you are ensuring each Power BI report user has appropriate data visibility. Without RLS, end users may have visibility into data they shouldn’t have access to (e.g., payroll or sales data), causing unforeseen issues for your organization.

When should I use Row-Level Security?

To determine if you need to implement RLS for your Power BI report,  consider whether your dataset includes sensitive information (e.g., company financials, customer information, patient information, payroll information, or employee information). Second, consider whether the end-users would need to apply filters to view the data they are looking for. If these statements are true, you will want to implement RLS into your report. 

What are some Row-Level Security Use Cases?

Below are example RLS use cases seen across many organizations:

1. Location Based RLS 🡪 I want a user only to see information within a specific location (City/State/Region/Country)

(Example: I am the Head of Finance for the west region and should only see data for that specific area)

2. Employee Based RLS 🡪 I want a user only to see information which they are tied to or responsible for (Email/Name)  

(Example: I am a Business Development Representative and should only see data for my CRM pipeline opportunities)

3. Business Line Based RLS 🡪 I want a user to only see information within a specific business line (Product/Service/Department/Division/Unit)

(Example: I am a Buyer for men’s clothing and should only see data for my specific division)

4. Job Title Based RLS 🡪 I want a user only to see information for which their job title is responsible

(Example: I am a Store Manager and should only see data that other store managers see)

5. Other RLS 🡪 Time-Based (Month/Year), Customer Based (Specific Customer/Group of Customers)

What are the Different Types of Row-Level Security?

There are two main types of RLS that you can implement into your Power BI report: Static RLS or Dynamic RLS. Static is the simpler of the two and requires a Power BI developer to define security logic manually within the PBIX file (explained later). Dynamic is the more complex type and requires logic to be defined within the PBIX file on the data model side using relationships (explained later).

When choosing between Static RLS and Dynamic RLS, here is a good rule of thumb:

1. Use Static RLS if:

  • You need to restrict data visibility for a specific group of users that require the same level of information (e.g., regional sales team to view data for their specific region)
  • Your report has fewer users and requires fewer security roles
  • Your report’s security logic is high level and straight forward (e.g., by user, region, or company)
  • Your user security requirements stay stagnant (security groups and security group users will not be changing frequently)
  • You won’t be adding or removing users frequently

Pros: Easy to set up and implement, minimal IT involvement

Cons: High maintenance, not reusable, manual

2. Use Dynamic RLS if:

  • You need to restrict data visibility for a specific group of users that require different levels of information (e.g., regional sales team to view data for their specific regional territory(s))
  • Your report has a greater number of users and requires a greater number of security roles
  • Your user security requirements often change (security groups and security group users will not be changing frequently)
  • Your report’s security logic is more complex (e.g., by job title, job function, department, division, location, territory, or combination)
  • Users will be frequently added or removed

Pros: Reusable security model, low maintenance, automated

Cons: Time consuming, requires dimension tables (users table/roles table), greater IT involvement

How to set up Row-Level Security in Power BI

Situation: A rapidly growing burrito chain in Minnesota, Ohio, and New York, hired Tessellation (a premier data analytics consulting firm) to develop a Power BI dashboard for its restaurant managers. The purpose of the dashboard is to share sales information so the restaurant managers can understand trends and make more informed business decisions. Management wants each restaurant manager to see only their states’ sales data. How can this be accomplished using Static and Dynamic RLS?

1. Static RLS:

Directions:

  1. Open Power BI Report
  2. Select ‘Modeling’ 🡪 Select ‘Manage Roles’
  3. Select ‘Create’ 🡪 Add a name to identify your new role (‘Minnesota,’ ‘Ohio,’ ‘New York’)
  4. Under ‘Tables’ 🡪 Select the vertical ellipsis 🡪 Select ‘Add filter’ 🡪 Select the field from table you want to filter by (‘State’)
  5. Add in logic for what you want to filter the field by (‘State = MN’, ‘State = OH’, ‘State = NY’) 🡪 Select check mark (‘Verify DAX Expression’) 🡪 Select save
  6. Validate: Select ‘Modeling’ 🡪 Select ‘View As’ 🡪 Select the role you just set up 🡪 Select ‘Ok’ 🡪 Verify that the data is filtering as expected
  7. Save/Publish Power BI Report to Power BI Workspace
  8. Go to Power BI Workspace
  9. On the dataset (in Orange) 🡪 Select the vertical ellipsis (‘More options’) 🡪 Select ‘Security’
  10. Add users to the roles (‘Minnesota’, ‘Ohio’, ‘New York’) you established🡪 Select ‘Save’
  11. Select the vertical ellipsis next to the security group (‘More options’) 🡪 Select ‘Test as role’ 🡪 Ensure that the RLS is working as expected

2. Dynamic RLS:

For Dynamic RLS you will perform similar steps as above, but you will use a dimension table (dim_users) to identify which location(s) each restaurant manager is responsible for. The benefit of using a dimension table is that it will always reflect the most current data and won’t require you to make manual security changes each time the data changes (like you would need to with Static RLS). The dimension table (dim_users) will be linked to the fact table (fct_sales) with the dimension table filtering the fact table.

Directions:

  1. Open Power BI Report
  2. Bring in dimension table
  3. Link dimension table to the fact table (with the dimension table filtering the fact table)
  4. Select ‘Modeling’ 🡪 Select ‘Manage Roles’
  5. Select ‘Create’ 🡪 Add a name to identify your new role (‘Users’)
  6. Under ‘Tables’ 🡪 Select the vertical ellipsis 🡪 Select ‘Add filter’ 🡪 Select the field from table you want to filter by (‘user_email’)
  7. Add in logic for what you want to filter the field by (‘user_email = userprincipalename()’) 🡪 Select check mark (‘Verify DAX Expression’) 🡪 Select save
  8. Validate: Select ‘Modeling’ 🡪 Select ‘View As’ 🡪 Select the role you just set up 🡪 Select ‘Ok’ 🡪 Verify that the data is filtering as expected
  9. Save/Publish Power BI Report to Power BI Workspace
  10. Go to Power BI Workspace
  11. On the dataset (in Orange) 🡪 Select the vertical ellipsis (‘More options’) 🡪 Select ‘Security’
  12. Add users to the role (‘Users’) you established🡪 Select ‘Save’
  13. Select the vertical ellipsis next to the security group (‘More options’) 🡪 Select ‘Test as role’ 🡪 Ensure that the RLS is working as expected

Conclusion

RLS can seem like a complex and daunting assignment, but my hope is that you now have a clearer understanding of what RLS is, why RLS is so important, and how you can start implementing RLS into your own Power BI reporting. As Power BI developers and data leaders, we have a responsibility to safeguard sensitive data and ensure that it is used appropriately. 

Additional Resources:

Are you looking to learn more about Row-Level Security in Power BI? Check out these resources! 

Ask us about all things Power BI! We are here to help!

Subscribe to Our 'Data Cap' Newsletter!

Want a monthly insights from the Tessellation Team with tips, tricks, and secrets to improve your analytics?

Sign up below and we'll deliver articles, events, and how to's straight to your inbox.
Comments are closed.