Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
HLVW
Helper I
Helper I

Row level security: Multiple users for one cost center

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. 

HLVW_0-1712849181611.png

What am I missing?

 

Pls. see enclosed sample:

https://we.tl/t-AEvwV0mbm4

 

Thanks a lot.

 

1 ACCEPTED SOLUTION

Hi @HLVW ,

 

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

MFelix_0-1712933632940.png

 

Then this will work properly:

MFelix_1-1712933653729.pngMFelix_2-1712933675128.png

File attached.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
AnalyticsWizard
Solution Supplier
Solution Supplier

@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
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

 

Hi @HLVW ,

 

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

MFelix_0-1712933632940.png

 

Then this will work properly:

MFelix_1-1712933653729.pngMFelix_2-1712933675128.png

File attached.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix .

 

It worked perfecly - thanks a lot.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors