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

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!