Default Security Roles

Hi, elijahc 

First, create a security table that maps users to their roles and the data they are allowed to access. This table can be stored in your database. I create a sample table:

During the ETL (Extract, Transform, Load) process, filter the data based on the user’s role and email. This can be done using SQL queries or a data transformation tool.

Example SQL Query:

SELECT *
FROM EmployeePerformance
WHERE (UserEmail = '[email protected]' AND AccessibleData = 'Employee1Data')
   OR (Role = 'Manager' AND AccessibleData = 'AllData')

Pass user-specific parameters to your Power BI queries to filter data dynamically. You can use Power Query to achieve this. Create a Parameter for User Email:

Name the parameter and set its value to UserEmail and USERPRINCIPALNAME()

In Power Query Editor, filter the data based on the parameter UserEmail

let
    Source = Sql.Database("ServerName", "DatabaseName"),
    EmployeePerformance = Source{[Schema="dbo", Item="EmployeePerformance"]}[Data],
    FilteredData = Table.SelectRows(EmployeePerformance, each ([UserEmail] = UserEmail or [Role] = "Manager"))
in
    FilteredData

Deploy your Power BI report and test it with different user accounts to ensure that the data is filtered correctly based on the user’s role and email.

Or just use dynamic RLS.

Select Manage Roles

Then create role named employee, and add the following DAX expression.

Create another role, such as Manager, without adding any filters.

In the Power BI service, assign these roles to the appropriate users.

Publish your Power BI report to the Power BI service. In the Power BI service, select your dataset and tap Security. Add the appropriate user or group for each role.

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.