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

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

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!