Row level security setup based on the column values in SQL Table

[aaedla]

Hi, 

I have sql tables as shown below. There is a relationship between tables.  Now I want to create power bi report with row level security.

Admins should be able to see all the data in all tables

Leader should be able to see only their data. If logged in user is leader for department 2 (Department name is IT), then only department 2 data should display in power bi.

If logged in user is neither admin, nor leader then no data should display.   

I tried to configure this based on below link but I didn't get expected results. Can anyone help me to implement this. Any help would be greatly appreciated.

Row Level Security 

Users Table  -  which is having Admin column Leader Column as shown below

ID	Name	FullName	Created Date	IsAdmin(boolean)	IsLeader
1	XXXX	XXXXX,YYYY	2024-10-29 15:40:45.270	1	NULL
2	yyyyy	yyyy, ZZZZ	2024-10-20 15:40:45.270	0	1
3	ZZZ	TTTTLDJL		0	2
4	PPP	TTTT		1	NULL
5	OXY	OXY		0	5
5	TT	WWW		0	NULL

Membership - This shows details of users memberships

ID	UserID	DeparmentsID	CreatedDate	CreatedBy
1	1	1	2024-10-29	XXXX
2	1	2	2024-10-29	XXX
3	1	3	2024-10-29	XXX
4	2	2	2024-10-29	YYYYY
5	2	5	2024-10-29	YYYY
6	3	4	2024-10-29	YYY
7	5	1
7	3	5	2024-10-29	YYY

Departments Names:

ID	Department Name	Status(Active/Inactive)
1	HR	Active
2	IT	A
3	Operations	A
4	Finance	A
5	Legal	A
6	Accounts	A

Expenses Details:

ID	Department ID	Budget	Expenses	Remaining
1	1	1000	500	500
2	2	2000	1400	600
3	3	1500	700	800
4	5	4500	2900	1600

[shafiz_p]

Hi @aaedla  Please check the below example:

I have 3 tables. User, Department and Expense. I have only relationship between department and Expense table. User table is out of the relationship. 

Only Admin user from User table have can see all my reports and the tagged as leader will be able to see reports of assigned department.

My relationship:

My unfiltered report :

I have just 3 department and 4 user, 2 with Admin tag and 2 with Leader tag. See the user table:

For admin, setup just role is admin. See images below:

Check view as :

For Leader write the below dax to filter:

I have first filter user table based on Role which is Leader and then created a list of department ID crosponding to Leader tag, in my case {1,2} and checked [Department ID] of Expense table is in the list or not. If then it will filter.

Check view as:

Now go to power bi and set up as @grazitti_sapna  mentioned and you are done. 

You need to adjust condition to filter user table based on UserName or Userprincipal name, for example :

IF (
    CALCULATE (
        COUNTROWS ( 'User' ),
        FILTER (
            'User',
            'User'[Email] = USERPRINCIPALNAME() &&
            'User'[Role] = "Leader"
        )
    ) > 0,
    [DepartmentID] IN 
    CALCULATETABLE (
        VALUES ( 'User'[DepartmentID] ),
        FILTER (
            'User',
            'User'[Role] = "Leader"
        )
    ),
    FALSE
)

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[grazitti_sapna]

Hi @aaedla ,

To implement row-level security (RLS) in Power BI based on the provided SQL table structure, you need to create roles and define DAX filters to control access. Here's a step-by-step approach:

1. Create Roles in Power BI:
   • You will define two roles: Admin and Leader. Users who do not fit into either role will not be able to see any data.

2. Set Up Relationships in Power BI:
   Ensure you have created the correct relationships between your tables in Power BI based on the IDs:

   • Users → Membership (on UserID)

   • Membership → Departments Names (on DepartmentsID)

   • Departments Names → Expenses Details (on Department ID)

3. Define DAX Filters for Roles:
   For each role, we will define filters to restrict data visibility.

   • Admin Role DAX Filter:
     Admins should see all data, so no additional filtering is required for them. Just create a role named Admin and assign users who have the IsAdmin column set to True.

   • DAX Expression:  [IsAdmin] = TRUE()

   • Leader Role DAX Filter:
     Leaders should see only the data related to their departments. We need to filter the Expenses Details table based on the departments the leader has access to. You can create a DAX expression that checks the logged-in user's ID and matches it with the DepartmentsID they lead.

   • DAX Expression for the Leader role:                                                                                                               [IsLeader] = TRUE() &&
     USERPRINCIPALNAME() IN VALUES('Membership'[UserID])

   • Here’s a breakdown:

     • [IsLeader] = TRUE(): Checks if the user is marked as a leader.

     • USERPRINCIPALNAME(): This function returns the email or login name of the currently logged-in user.

     • VALUES('Membership'[UserID]): Ensures that the user belongs to the department based on the Membership table.

   • Alternatively, if the leader's assignment is tied to specific departments, use a relationship to filter the relevant departments directly based on USERPRINCIPALNAME().
4. No Data for Other Users:
   Users who are neither Admins nor Leaders will not have any data visible because no roles are assigned to them.

5. Assign Users to Roles:
   In Power BI Desktop:

   1. Go to the Modeling tab and select Manage roles.

   2. Create two roles: Admin and Leader.

   3. For the Admin role, apply the DAX filter [IsAdmin] = TRUE().

   4. For the Leader role, apply the DAX filter [IsLeader] = TRUE() && USERPRINCIPALNAME() IN VALUES('Membership'[UserID]).

   5. Publish the report to Power BI Service.
   6. In the Power BI Service, under the Security settings of your dataset, assign email addresses to the corresponding roles.

Example of Role-Based Visibility

• Admin (IsAdmin = True): Can see all departments and their expenses.
• Leader (IsLeader = True): Sees only departments they lead.
• Other Users (No roles assigned): See no data.

Testing the Setup

• You can test the setup in Power BI Desktop by going to Modeling > View as Role to simulate different roles.
  This configuration will ensure that admins see all data, leaders see their specific departments, and other users see no data.

If I have resolved your question, please consider marking my post as a solution🎉. Thank you!

[aaedla]

@grazitti_sapna 

Thank you for your comments.

here I need to diplay users table as welll. If logged in user is department A leader then I should display all users details associated to department A. If user is associated to department A and department B then report should display user for department a leader as well as department b leader.

I have updated user table with more rows  for your refeerence.

Here I am getting confused. Any suggestion would be greatly appreciated.