Designing Role-Based Data Views with RLS in Power BI Desktop

Introduction

A key component of business intelligence is data protection. Frequently, a report’s data should not be accessible to all stakeholders. Power BI’s Row-Level Security (RLS) changes everything in this situation. Using filters specified, RLS limits users’ access to data. It enhances performance and privacy by guaranteeing that users only view what they are permitted to see.

Pioneers in Cloud Consulting & Migration Services

Reduced infrastructural costs
Accelerated application deployment

Get Started

Getting Started with RLS in Power BI

Step 1: Load Sample Data (Eg. Sales Data)

This can be loaded via an Excel or CSV file or by selecting “Enter Data” in Power BI Desktop.

Step 2: Define the Role

Select Modeling Tab.
2. Select Manage Roles.
3. Create a new role – for example: Manager_Role.
4. Select data table (e.g., SalesData).
5. Enter below DAX expression:

[Manager] = USERNAME()

If using email address in Power BI Service, then:

[ManagerEmail] = USERPRINCIPALNAME()

Step 3: Save and Close

Click OK to save the role.

Testing RLS Roles Within Power BI Desktop

Simulate RLS in Power BI Desktop

Once the Role has been created, select Modeling > View As.
2. Verify the Manager Role that you generated.
3. Enter a user’s name (for, e.g., ‘A’) or, if your data uses email, their email address.
4. Press OK.
Your visuals will display only the rows where Manager = ‘A’.

Example: If you’re logged in as A, you’d only see:

bi2

Publish to Power BI Service

Publish the report to your workspace.
Select Dataset section > Security of your dataset.
Add users (e.g., a@company.com) to the correct role.
Share report. Each user will be able to see their data based on the roles assigned.

Advantages of using RLS

More than just a means of concealing data, Row-Level Security (RLS) is a potent tactic that combines performance optimization, data governance, and customized reporting into a single solution. Below mentioned are the advantages:

Improved Security of Data

RLS ensures that each user can only access the data they are authorized to see. This is especially crucial for businesses that handle sensitive or private data, such as financial figures, human resources records, or client information. Restricting access at the data level significantly reduces the chances of both accidental and intentional data exposure. A regional manager, for instance, will only view sales data for their territory.

Enhanced Efficiency
Only the part of data pertinent to the user seeing the report is loaded by Power BI when RLS is used. This can result in more effective query execution and quicker load times, particularly in reports with big datasets. Report display is optimized as a result of processing and visualizing less data, which enhances the user experience.
Streamlined Report Management
If RLS isn’t available, you might have to make distinct reports or datasets for various departments or roles. By constantly filtering data, RLS enables a single report to serve many users. This makes maintaining reports easier, reduces redundancy, and guarantees uniformity throughout the company.
Assistance with Compliance
Companies must frequently abide by industry-specific standards, external laws like GDPR and HIPAA, or internal procedures. By preventing users from accessing data to which they are not legally entitled, RLS aids in compliance efforts. During security reviews or compliance audits, firms may show that the right measures are in place with this audit-friendly control over data access.

Conclusion

By using RLS, reports are guaranteed to be secure and informative. RLS enables you to manage user visibility without creating duplicate datasets or reports, whether you’re working with clients, department heads, or regional managers.

You may improve data confidentiality and provide a more customized data experience with a basic DAX filter and some setup.

Drop a query if you have any questions regarding Power BI and we will get back to you quickly.

Empowering organizations to become ‘data driven’ enterprises with our Cloud experts.

Reduced infrastructure costs
Timely data-driven decisions

Get Started

About CloudThat

CloudThat is a leading provider of Cloud Training and Consulting services with a global presence in India, the USA, Asia, Europe, and Africa. Specializing in AWS, Microsoft Azure, GCP, VMware, Databricks, and more, the company serves mid-market and enterprise clients, offering comprehensive expertise in Cloud Migration, Data Platforms, DevOps, IoT, AI/ML, and more.

FAQs

1. Explain difference between USERNAME() and USERPRINCIPALNAME()?

ANS: –

USERNAME() returns the username (e.g., A), which is more prominently used on the desktop.
USERPRINCIPALNAME():- returns mail ID (e.g., a@abc.com) – recommended in Power BI Service.

2. Can we assign multiple users the same role?

ANS: – Yes, assigning more than one user or security group to a single role within the Power BI Service is possible.