Row-Level Security

[Ilgar_Zarbali]

Row-level security (RLS) in Power BI allows you to limit data access for specific users by applying row-level filters within defined roles. In the Power BI service, users with workspace access can view semantic models within that workspace. However, RLS only affects users with Viewer permissions and does not apply to Admins, Members, or Contributors.

RLS can be set up for data models imported into Power BI or for semantic models using DirectQuery, such as SQL Server. For live connections to Analysis Services or Azure Analysis Services, RLS must be configured within the model itself rather than in Power BI, as the security option is not available for live connection semantic models.

I am analyzing a small sales dataset from multiple retail stores located in different cities across Azerbaijan, including Baku, Ganja, Gabala, and Ismayilli.

In this article, I will demonstrate how to set up Row-Level Security.

First of all, let's use the Username DAX function to identify the computer we are working on.

Username = USERNAME()

Let's add a Card Visual to the Canvas and place the newly created Username measure in it.

In the visual, we will see the name of the computer we are working on.

Once we publish the report to Power BI Service, it will display the name we used to log into the Service.

In the dataset of our Power BI report, there are 9 stores. Ilgar Zarbaliyev (E-Mail: ilgar.zarbaliyev@sumproduct.com) is responsible for Store 1, Store 2, Store 3, Store 4, and Store 5; Kathryn Newitt (kathryn.newitt@sumproduct.com) is responsible for Store 6 and Store 7; and Liam Bastick (liam.bastick@sumproduct.com) is responsible for Store 8 and Store 9. As the number of stores increases, the names of the responsible individuals may change. Therefore, we need to make this dynamic.

We can achieve this by adding an additional column to the dStore Table.

Let's add a column named Dynamic Manager and write the following formula in the browser:

Manager for Store = 
IF(
    dStores[Store ID] IN {"Store 1", "Store 2", "Store 3", "Store 4", "Store 5"}, "ilgar.zarbaliyev@sumproduct.com",
    IF(
        dStores[Store ID] IN {"Store 6", "Store 7"}, "kathryn.newitt@sumproduct.com",
             "liam.bastick@sumproduct.com"
        )
    )

The table will look something like this after applying the Dynamic Manager column formula:

Let's publish our report from Power BI Desktop to the Newsletter Workspace in Power BI Service.

In the Newsletter Workspace, click the three dots (ellipsis) on the right side of the Study_Cross-Selling_File semantic model. From the dropdown menu, select Security.

In the Members text box, let's add the following email addresses:

• ilgar.zarbaliyev@sumproduct.com
• kathryn.newitt@sumproduct.com
• liam.bastick@sumproduct.com
• oscar.hagan@sumproduct.com

First, click Add, then click Save to apply the changes.

Now, let's test the roles.

To do this, click the ellipsis next to the Dynamic Manager text and select Test as role.

The pop-up window will appear as follows.

Click the Dynamic Manager drop-down button and select Select Person.

Then, enter ilgar.zarbaliyev@sumproduct.com from the predefined roles.

Since the selected role belongs to the report author, all data will be visible. The following message will appear:

"Keep in mind that the impersonated user has write permission to the dataset. No Row-Level Security roles will be applied."

Now, let's select the kathryn.newitt@sumproduct.com role.

The report will now display only the data related to the stores that Kathryn is responsible for.

Now, let's select Oscar Hagan, who is included in the Dynamic Managerlist but is not responsible for any store.

A window like the following will appear, indicating that no data is available for this user.

Now, let's test Tim, who is neither responsible for any store nor included in the Dynamic Manager list.

A window will appear showing that he has no access to any data.

Conclusion

Implementing Row-Level Security (RLS) in Power BI ensures that users only see the data relevant to them. By dynamically assigning managers to stores and testing different roles, we verified that access is properly restricted. This approach enhances data security and provides a personalized reporting experience. Always test roles to confirm that permissions work as expected before publishing reports to Power BI Service.

[nuraddinmalikov]

@Ilgar_Zarbali Thank you for the valuable information.

[Vika_Shalbuzova]

@Ilgar_Zarbali Thank you for the valuable information.

[Miraligafarli04]

Great