Set up row-level security in Power BI paginated reports

Article
12/05/2023

APPLIES TO: Power BI Report Builder Power BI Desktop

This article explains how to set up row-level security (RLS) for paginated reports in Power BI. If your paginated report is based on a Power BI semantic model, the article Row-level security (RLS) with Power BI provides a solid background for setting up RLS to use in your paginated report.

To use RLS in a paginated report, you first create parameters in that report. Then you take advantage of the built-in field UserID:

Use UserID in a filter.
Use UserID in a query.

Note

Value for UserID expression during report preview in Power BI Report Builder could be in a different format than the value for UserID expression when run in a report on the Power BI service. It depends upon whether the computer running Power BI Report Builder uses Active Directory or Microsoft Entra ID.

Prerequisites

License requirements for Power BI paginated reports (.rdl files) are the same as for Power BI reports (.pbix files).

You don't need a license to download Power BI Report Builder from the Microsoft Download Center for free.
With a free license, you can publish paginated reports to My Workspace in the Power BI service. For more information, see Feature availability for users with free licenses in the Power BI service.
With a Power BI Pro license or a PPU license, you can publish paginated reports to other workspaces. You also need at least a contributor role in the workspace.
You need Build permission for the dataset.

For row-level security (RLS) to work in Power BI Report Builder, You need at least a Viewer role in the dataset workspace, and Build permission for the dataset. Read about roles in workspaces.
You create paginated reports using the SQL Server Reporting Services engine, and not the Power BI (Analysis Services) engine, so you set up RLS filtering in Power BI Report Builder.

Create a parameter using UserID

To apply row-level security to a Power BI paginated report, the first step is to create a parameter and assign the built-in field UserID. See the article Create parameters if you need help creating them.

Then you use this parameter with UserId in a filter or in a query to the data source.

Use UserID in a filter

In the Dataset Properties window, from the left pane, select Filter.
From the Expression dropdown menu, select the parameter you want to use for filtering the data.
Select the Value function button.
In the Expression window, from the Category list, select Built-in Fields.
From the Item list, select UserID > OK.
In the Dataset Properties window, verify that the expression is your selected parameter = UserID.
Select OK.

Use UserID in a query

In the Dataset Properties window, from the left navigation pane, select Parameters, then select Add.
In the Parameter Name field enter @UserID, and in the Parameter Value add [&UserID].
From the left pane, select Query. In Query, add the UserID parameter as part of your query.

This screenshot uses the color parameter as an example *(WHERE FinalTable.Color = @UserID)*. If needed, you can create a more complex query.
Select OK.