By clicking “Accept”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. View our Privacy Policy for more information.
Jun 13, 2024
Power-BI

How to set up Row Level Security in Power BI?

Data Privacy with Power BI Row Level Security. By Sanyukta Suman

Welcome to the seventh chapter of The Complete 2023 Beginner’s Guide to Power BI. If you’re just starting with Power BI, we recommend reading the first chapter “The Complete 2023 Beginner’s Guide to Power BI”, which provides a basic overview of Power BI, including how to connect to data sources, create simple reports, and visualize data. For those looking to enhance the aesthetic of your Power BI dashboard, we recommend checking out our most popular chapter, “How to create a stunning Power BI Dashboard Theme”.

What you will learn

In this blog, we'll delve into Row-Level Security (RLS) within Power BI, a feature enabling precise control over user access to data in reports or datasets. Unlike object-level security, which operates on columns or tables, RLS establishes horizontal boundaries, dictating data visibility based on defined rules for each row. We'll explore the significance of RLS in maintaining data security and how to implement it effectively, covering key steps from role creation to testing within Power BI Service Cloud.

What is Row-Level Security in Power BI?

Row-Level Security (RLS) with Power BI  is a way to control what data users can see in a Power BI report based on certain rules applied to each row of data. Meaning you can filter charts based on user Access in your Power Bi Dashboard. It's like setting boundaries for who can see what in a horizontal way, unlike object-level security which focuses on columns or tables.

Implementing RLS involves giving instructions or rules to Power BI, so it knows how to filter data for different users.

RLS is important for keeping data secure because it ensures that each user only sees the data relevant to them. Without RLS, users might see sensitive information they shouldn't have access to, like payroll or sales data.

Benefits of Row-Level Security

Enhanced Data Security: RLS ensures that sensitive information is protected by restricting access to authorized users only. This prevents unauthorized users from viewing confidential data, such as financial records or personal information.

Compliance Adherence: RLS helps organizations comply with regulatory requirements, such as GDPR or HIPAA, by controlling access to sensitive data and ensuring that only authorized individuals can view it.

Customized Data Access: With RLS, organizations can tailor data access based on user roles, responsibilities, or specific criteria. This ensures that each user sees only the data relevant to their job function, improving efficiency and focus.

Reduced Risk of Data Leakage: By implementing RLS, organizations minimize the risk of data leakage or exposure. This is particularly important in industries where data privacy and confidentiality are paramount, such as healthcare or finance.

Streamlined Data Governance: RLS facilitates centralized management of data access rules within Power BI, making it easier for administrators to maintain control and enforce policies across reports and datasets.

Improved Decision-Making: By providing users with access to the right data at the right time, RLS enables more informed decision-making processes. Users can confidently rely on the accuracy and relevance of the data they're viewing.

Efficient Resource Allocation: RLS helps optimize resource allocation by directing users to the specific data they need, rather than overwhelming them with irrelevant information. This improves productivity and ensures resources are used efficiently.

Use Cases of Row-Level Security

Location-Based RLS: Restricting users to see only data related to specific locations.

Let's consider a multinational retail company with stores in various countries. The company wants to provide sales reports to its regional managers but restricts access to data specific to their region only. With Location-Based RLS in Power BI, the company can implement filters so that each regional manager can only view sales data for their designated region. For example, the regional manager for Europe would only see sales data for European countries like France, Germany, and Italy, while the regional manager for Asia-Pacific would only see sales data for countries in that region like Japan, China, and Australia. This ensures that each manager focuses on their assigned region's performance without accessing sensitive data from other regions.

Employee-Based RLS: Limiting access to data based on a user's role or responsibility.

In a large organization with multiple departments, each department has its own set of employees and projects. The HR department needs access to all employee data for HR-related tasks, while the finance department needs access to financial data for budgeting and forecasting. With Employee-Based RLS, Power BI can be configured to restrict data access based on a user's role or department. For example, HR managers would have access to employee records and HR-related data, while finance managers would have access to financial reports and budget data. This ensures that each employee sees only the data relevant to their role or responsibility, maintaining data confidentiality and security within the organization.

​​Now that we have a good understanding of row-level security, let's proceed to learn the steps for setting it up.

How to set up Row Level Security

Setting RLS in Power BI Desktop

  1. On the top ribbon, select Modeling  → then Manage Roles
  2. Select Create → add a name to identify your new role (‘Users’)
  3. Under Tables → select the three dots () → select Add filter and then choose the field from the table you want to filter by, example: [country]
  4. Add the logic for what the field should filter by, example [country] = “Germany”→ select the check mark (Verify DAX Expression) → select Save
  5. Similar for user you set a filter as [user]  = USERPRINCIPALNAME()

Note: The Userprincipalname function is used to identify the name of the login user.

Validating and testing in Power Bi Desktop:

Select Modeling → select View As → select the role you just set up (example: country) → select Ok → verify that the data is filtering as expected.

In Power BI Service Cloud

  1. Save and publish the Power BI report to a Power BI Workspace
  2. Navigate to the Power BI Workspace
  3. On the database → Select the three dots (...) → select Security
  4. Add users to the role (‘Users’) you established (for example, users who will be accessing the Power BI dashboard later) → select Save
  5. Select the three dots (...) next to the security group (More options) → select Test as role → ensure that the RLS is working as expected

Conclusion

In conclusion, Row-Level Security (RLS) in Power BI provides a crucial means of controlling data access based on specific rules applied to each row. It ensures data security by limiting user visibility to relevant information and facilitates compliance with regulations like GDPR or HIPAA. By customizing data access, RLS enhances decision-making processes and resource allocation while reducing the risk of data leakage. Practical applications include location-based restrictions and employee-based access control.

If you're looking to expand your Power BI expertise, don't miss out on our insightful blog posts. Explore "6 Design Tips to Create Stunning Power BI Dashboards" for effective ways to enhance dashboard aesthetics. Dive into strategies for optimizing performance with our guide on "Maintaining Your Dashboard Under the 1 GB Limit." Additionally, discover innovative solutions to common Power BI challenges in "5 Creative Approaches to Using Power BI Bookmarks." Happy learning!

Further Links

This post is part of the Complete Guide to Power BI  Data Analytics series from datadice and explains to you every month the newest features of Power BI.

Follow us on LinkedIn for insights into our daily work and important updates on BigQuery, Data Studio, and marketing analytics.

Subscribe to our YouTube channel for discussions on Data Warehouse, BigQuery, Looker Studio, and Google Tag Manager.

If you are interested in learning BigQuery from scratch, get access to our free BigQuery Course

Elevate your skills with Google Data Studio and BigQuery by enrolling in our Udemy course.

Need help setting up a modern, cost-efficient data warehouse or analytical dashboard? Email us at hello@datadice.io to schedule a call.