Row Level security

[ThomasWeppler]

Hi Power BI community

I want project leders to only be able to see users in their team. My problem is that a project leader can lead multiple teams and they can lead the same team as another project leader.

Table: User
Name: (text) the users name

User_id: (number) a uniq number for each user

Team_id: (number) a uniq number for each team. Multiple users can be in the same team

Leader_team_ids: (text) A list of numbers that shows all the teams a user is a leader of. example: if Leader_team_ids = 10,14,15,16 than it means that the user is the leader of team 10,14,15 and 16

Emal: (text) the users e-mail adresse which I use for RoW level security.

All help is greatly appreciated.

[ThomasWeppler]

@bhanu_gautam Thanks for the solution. 🙂

[bhanu_gautam · Accepted solution]

@ThomasWeppler You can create this table in Power BI using DAX or import it from your data source.

Ensure that the Leader table has a relationship with the User table on the Team_id.

Go to the Modeling tab in Power BI Desktop.
Click on Manage roles.
Create a new role and use the following DAX expression to filter the User table:

[Email] = USERPRINCIPALNAME() &&
CONTAINSSTRING([Leader_team_ids], SELECTEDVALUE('User'[Team_id]))

After publishing the report to the Power BI service, assign the created role to the respective project leaders.