Row level security: Multiple users for one cost center

[HLVW]

Hi.

I need to add row level security to a report showing costs per department. All departments have a manager, who should have access to data for the specific department. Furthermore, the finance department should have access to most departments.

I have 3 tables: 

- Actual postings on department level

- List of departments (needed as I also have budget figures in another table)

- Access table with employees (name, email, department etc.)

I have added a role and added the e-mail addresses in the workspace as well, but no data is shown in the app. 

What am I missing?

Pls. see enclosed sample:

https://we.tl/t-AEvwV0mbm4

Thanks a lot.

[AnalyticsWizard]

@HLVW 

To implement row-level security (RLS) in your Power BI report for showing costs per department, with specific access rules for department managers and the finance department, you'll need to set up RLS within Power BI Desktop. This involves creating roles and defining DAX rules that control access based on user credentials and the data model relationships.

Here's a step-by-step guide on how to set up RLS for your scenario:

### Step 1: Organize Your Data Model

Ensure that your tables are related appropriately in your Power BI model. Typically, your setup might look like this:

- **Actual Postings Table**: Contains transactions with a department identifier.
- **Departments Table**: Lists all departments, possibly with a unique department ID.
- **Employees Table**: Includes employee details such as name, email, and their department.

Make sure that the Departments Table is linked to both the Actual Postings Table and the Employees Table by the department ID or name.

### Step 2: Create RLS Roles

In Power BI Desktop, go to the **Modeling** tab and select **Manage Roles**. Create roles based on the access requirements:

#### Manager Access

1. **Create a New Role** named "Department Manager".
2. For each table (Actual Postings, Departments, etc.), add a DAX filter expression that restricts access. For instance, for the Actual Postings Table, you might use:
```DAX
Actual_Postings[DepartmentID] = LOOKUPVALUE(Employees[DepartmentID], Employees[Email], USERPRINCIPALNAME())
```
This expression ensures that managers can only see data related to their department, assuming that the user’s email is used as the login credential.

#### Finance Access

1. **Create another Role** named "Finance".
2. Add a DAX filter to provide broader access. You might decide that the finance team should see all departments except for a few restricted ones (if applicable). For example:
```DAX
NOT(Actual_Postings[DepartmentID] IN {"DeptID1", "DeptID2"}) // Assuming DeptID1 and DeptID2 are restricted
```
Adjust this based on your specific requirements.

### Step 3: Assign Users to Roles

After publishing your report to the Power BI Service, you need to assign users to these roles:

1. Go to your workspace where the report is published.
2. Click on the dataset settings, then select the **Security** tab.
3. You can add users to the roles you created by entering their email addresses.

### Step 4: Test Your RLS Setup

Before going live, it’s crucial to test the RLS setup to ensure that it functions as expected:

1. In Power BI Desktop, use the “View as Roles” feature located in the Modeling tab to simulate the experience of different users.

2. In the Power BI Service, you can also test by logging in as a user assigned to a specific role (if you have access to such credentials).

### Additional Considerations

- **Budget Figures**: If your budget figures also need RLS, apply similar DAX expressions to those tables.
- **Performance**: Complex DAX expressions can impact report performance. Monitor and optimize as necessary.
- **Updates**: If department managers or structures change, update the roles and assignments accordingly.

Implementing RLS can significantly enhance data security and ensure that individuals only see data pertinent to their roles, which is critical in financial and operational reporting.

[MFelix]

Hi @HLVW ,

For this you need to have a table dimension that has the departments access per user, then make a relationship between those users and the department table (assume you have one) then you should use the RLS on the table of Users/Departments that will flow trough the rest of the model.

https://www.fourmoo.com/2018/02/20/dynamic-row-level-security-is-easy-with-active-directory-security...

[HLVW]

Hi @MFelix 

I think I have done hwat you are suggesting, but it still doesn't work. I have added a test report to my question.

Thanks a lot.

[MFelix · Accepted solution]

Hi @HLVW ,

You need to have your relationship with many-to-many with the filtering from the UserAcess to the CostCenter:

Then this will work properly:

File attached.

[HLVW]

Hi @MFelix .

It worked perfecly - thanks a lot.