Row level security that gives access to different teams based on text string

[ThomasWeppler]

Hi Power BI communnity

I want to give some team leaders access to see the performance of employees in their team with Row level security.
I have a table with the following colums.

User = table name
Index = [number] an index column
Userid = [number] a uniq id for each user
teamid = [number] an id for the team the employee belongs to. multiple users can be in the same team

Leaderid = [text] a list of all the teams a user is the leader of. (Ex. 5,7,8,10)

Diffrent users can be the leader of the same team.
If a user is the leader of (5,7,8,10) he should be able to see all users with the team id IN {5,7,810}
What is the correct way to solve this with Row level security?

All help and feedback is greatly appreciated.

[devesh_gupta]

@ThomasWeppler 

To implement Row-Level Security (RLS) in Power BI for team leaders to only see the performance of employees in their teams, you can follow these steps:

1. Create a Relationship:

   • In Power BI Desktop, create a relationship between the teamid column in your main data table (let's call it PerformanceData) and the teamid column in your User table.

2. Create a Role in Power BI:

   • Go to the "Model" view in Power BI Desktop.

   • Under the "Model" view, locate the "Manage Roles" option in the ribbon.

   • Click "Manage Roles" to open the "Model View" dialog.

3. Create a Role for Team Leaders:

   • Create a new role called "Team Leader" (or any name that makes sense to you).

   • Define a DAX expression that filters data based on the Leaderid column. This expression should allow users with the role to see only the data for the teams they lead.

   Example DAX Expression:

PerformanceData[teamid] IN VALUES(User[teamid]) && 
ISNUMBER(FIND(User[Userid],[@Userid]))

In this example, [@Userid] refers to the user viewing the report. The expression checks if the teamid of the performance data matches any of the teamid values in the User table for which the user is the leader.

• Assign Users to Roles:

  • In the "Model View" dialog, you can add users or groups to the "Team Leader" role. This will grant them the access defined by the DAX expression.

• Test RLS:

  • In Power BI Desktop, use the "View as Role" feature to test if RLS is working as expected for team leaders.

• Publish to Power BI Service:

  • Publish your report to the Power BI Service.

• Configure Gateway (if needed):

  • If your data source is on-premises, ensure that you have set up and configured a Power BI Gateway to enable access to your data.

• Assign Roles in Power BI Service:

  • In the Power BI Service, navigate to the dataset settings, go to the "Security" tab, and assign users to the appropriate roles.

  • Add the users who are team leaders to the "Team Leader" role.

• Test in Power BI Service:

  • As a team leader, log in to the Power BI Service and verify that you can only see the data for the teams you lead.

This approach uses RLS to dynamically filter data based on the team leadership information. It allows each team leader to see only the data for the teams they are responsible for.

If you find this insightful, please provide a Kudo and accept this as a solution.