cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
ThomasWeppler
Continued Contributor
Continued Contributor

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

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.

 

 

 

 

 

1 REPLY 1
devesh_gupta
Solution Supplier
Solution Supplier

@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.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors