How to Generate Power BI Report Based on Logged In User

Showing a report viewer only the data that’s relevant to them is a common and critical requirement for any data dashboard. Manually creating separate reports for each person isn’t practical, and you certainly don't want to expose sensitive information to the wrong audience. This tutorial will walk you through exactly how to configure your Power BI report to automatically filter data based on the logged-in user.

What is Row-Level Security (RLS) in Power BI?

The feature that makes this possible is called Row-Level Security, or RLS. In simple terms, RLS is a gatekeeper for your data. Instead of restricting access to the entire report, you define rules that filter the data at the row level. When a user opens a report, Power BI checks who they are, looks up the rules you’ve set for them, and only shows them the specific rows of data they are permitted to see.

For example, if you have a sales dashboard for the entire company, you can use RLS to ensure:

A sales representative for the West region only sees sales figures for the West region.
A regional manager sees data for all representatives within their region.
A national sales director sees the data for all regions combined.

They are all looking at the same report, but their view is automatically tailored to their role. This not only protects sensitive data but also provides a more focused and relevant user experience. The method we'll cover is "dynamic" RLS, which automatically uses the signed-in user's identity to filter the data without you having to create hundreds of rules for each individual person.

What You'll Need Before You Start

Before jumping into the setup, make sure you have the following pieces in place. Getting organized now will make the whole process much smoother.

Power BI Desktop: This is the free application where you'll build your report and define the security rules.
A Power BI Pro or Premium Per User (PPU) license: RLS rules are applied in the Power BI Service (the web version), and sharing reports with this functionality requires a paid license for both you and the report viewers.
A Dataset with User Information: Your data model needs a way to associate data rows with specific users. Most often, this is done with an employee or customer email address. For instance, your sales table might have a column with the email of the salesperson who closed the deal.
Clearly Defined Roles: You should have a clear idea of how you want to filter your data. Start simple. For this guide, our goal is straightforward: "Each user should only see the rows of data associated with their own email address."

Step-by-Step Guide: How to Filter a Power BI Report Based on a Logged-in User

Once you have your prerequisites in place, you’re ready to implement dynamic RLS. We'll use a common scenario: filtering a sales report so that each sales representative can only see their own sales data.

Let's assume you have a data model with at least two tables:

A Sales table containing transaction data (e.g., OrderID, Product, Revenue).
A SalesReps table with information about the salespeople (e.g., SalesRepID, Name, Email).

Make sure these tables are related in your data model, typically by SalesRepID.

Step 1: Create a Security Role in Power BI Desktop

First, we need to create a "role" that will contain our filtering rule. A role is simply a named container for one or more RLS rules.

In Power BI Desktop, go to the Modeling tab on the top ribbon.
Click on Manage Roles.
In the Manage roles window, click Create.
Give your new role a descriptive name. For this example, let's name it Sales Rep View. A good name helps you remember what the role is for later.

Step 2: Define the DAX Filter Expression

This is where the magic happens. We need to write a simple DAX (Data Analysis Expressions) formula that tells Power BI how to filter the data for the Sales Rep View role.

In the Manage roles window, with your Sales Rep View role selected, find the table that contains the user's email address. In our scenario, this is the SalesReps table.
Click the ellipsis (...) next to the SalesReps table and select Add filter, then choose the column containing the email addresses (e.g., [Email]).
In the Table filter DAX expression box, you will replace [Value] with your rule. This rule will use a special DAX function that automatically detects the logged-in user. Type the following expression:

What Does This DAX Formula Do?

This simple line of code is incredibly powerful. Here's a quick breakdown:

[Email] refers to the column in your SalesReps table that holds each representative's email address.
= is the logical operator, checking for a match.
USERPRINCIPALNAME() is a DAX function that returns the User Principal Name (UPN) of the person currently signed into Power BI. In almost all cases, this is their Power BI login email address.

Essentially, this expression translates to: "For the user viewing this report, filter the SalesReps table to only show the row where the value in the 'Email' column matches their login email." Because your SalesReps table is related to your Sales table, Power BI will automatically filter the sales data as well.

After entering the formula, click Save.

Step 3: Test Your Role in Power BI Desktop

Before publishing your report, you must test the role to make sure it's working as expected. Power BI Desktop has a feature that lets you impersonate a user to see the report through their eyes.

On the Modeling tab, click View as.
In the View as roles window, check the box for the role you just created, Sales Rep View.
You also need to specify which user you want to test. Check the box for Other user and enter the email address of a sales rep from your database (e.g., sara.jones@yourcompany.com).
Click OK.

Your report canvas will now reload. You should see a yellow bar at the top indicating that you are viewing the report as 'Sales Rep View' and the test user. All the visuals on your report should now only display data associated with Sara Jones. This confirms your RLS rule is working correctly! To stop viewing as the role, just click the Stop viewing button in the yellow bar.

Step 4: Publish Your Report to the Power BI Service

Now that your RLS is created and tested, it’s time to publish the report to the Power BI Service where your team can access it.

From the Home tab in Power BI Desktop, click Publish.
Select a workspace to publish the report to.
Wait for the confirmation message, and then you can open the report in the Power BI Service.

Step 5: Assign Users to Roles in the Power BI Service

The final step is to tell Power BI which users belong to the Sales Rep View role you created. This is done in the security settings for the dataset (not the report).

Navigate to the workspace where you published your report.
Find the dataset for your report (it will have the same name), click the ellipsis (...), and select Security.
You will see the role Sales Rep View that you created in Power BI Desktop. Select it.
A box will appear where you can add users. Start typing the names or email addresses of the sales reps you want to have this filtered view. You can also add email-enabled security groups or distribution lists from Office 365.
After adding your members, click Add and then Save.

That's it! Now, when any of the users you added to this role open the shared report, the dynamic RLS rule will kick in. Power BI will detect their login email using USERPRINCIPALNAME(), compare it to the email column in your data, and show them only their own data.

Advanced Tips and Common Scenarios

While the basic user filter is incredibly useful, you might encounter more complex security needs.

How to handle manager and team hierarchies?

What if a manager needs to see their own data and the data of everyone who reports to them? This requires a more complex DAX expression and a proper hierarchy defined in your data (e.g., an Employees table with an 'Employee Email' and a 'Manager Email' column). You might use DAX functions like PATH() and LOOKUPVALUE() to traverse this hierarchy, but the core principle of RLS remains the same.

What if my users see a blank report?

This is a common issue and almost always means one of two things:

Data Mismatch: The email address the user signs into Power BI with does not exist or does not perfectly match a value in your user/email column. Check for typos or formatting differences (e.g., john.doe@company.com vs. John.Doe@company.com).
No Data to Show: The user is correctly identified, but they have no associated data in the fact tables for the current report filters (like the selected date range).

Final Thoughts

Implementing row-level security lets you build a single, impressive report that automatically tailors its view for different users. By using the USERPRINCIPALNAME() function in your DAX formula, you can dynamically show each logged-in user just their slice of the data, enhancing both security and usability.

While configuring RLS in Power BI is a powerful way to manage data access within an organization, we know that getting all your data in one place to begin with is often the biggest challenge. The typical marketing or sales team's data is scattered across a dozen platforms like Google Analytics, Shopify, Facebook Ads, and Salesforce. Here at Graphed, we automate that entire process. You can connect your marketing and sales sources in a few clicks, and then simply ask for the dashboards and charts you need in plain English. No more wrestling with DAX or complicated security models - just instant, shareable insights that empower your whole team to make better decisions.