Dealing with RLS and many-to-many relationship

[KatTheDuck]

Hi,

I have been learning power bi for some time and recently I "inherited" a report from a person who no longer works in my company. I was asked to refresh it, so it wasn't difficult, but when I looked at the model, it seemed to me that it was overcomplicated and that there were many many-to-many relationships in it.

I tried to create a new model that would meet all the requirements, but I can't figure out how to structure the thread to avoid creating many-to-many relationships.

The company structure is complicated. I will present it with false data.

1. An employee may work in several departments.

2. The full sales results of each employee are to be seen by the manager and his deputy in the department in which the employee is employed.

3. Additionally, employees can sell products that are assigned by default to the department in which they are not employed. Then the manager of this department must see its results only in the part that concerns his department. So if an employee is employed in two departments, his full results are seen by as many as 4 people. If he sells a product from another department, 2 more people must also see some of the results. Therefore, it is impossible to obtain a unique table with managers and deputies that can then be used for RLS.

The employee table is very detailed, but managers insist that they need to filter data by the columns available in the table. They want to know what sales look like based on the results of faculty employees and, additionally, employees from outside the faculty.

We are using RLS so many-to-many relationship does not filter data correctly.

I don't know where to start with this. I hope what I wrote is understandable. If you have any suggestions, I would be grateful. I would like to improve it, because it seems to me that it is not done completely correctly, and I am running out of ideas.

Example sales table. You can take department code from product code - it's first 2 digits

Example users table which should be used to apply RLS.

Employee table. It is very detailed. Sales code is taken from sales table, DEP_CODE is code of department that correspond with user table.

So the way it was tried to be done was that user table should be connected to employee table by DEP_CODE, and then user_ID and SALES_CODE combined as key (so for example "1x24") connected to sales table with the same key logic.

But it doesn't work due to many-to-many relationship. Any ideas?

[lbendlin]

You cannot always avoid many to many relationships. What you must avoid if you want RLS to be successful is bidirectional many to many relationships, or worse, those and RLS on both sides.  You want your RLS filters to flow only in one direction - from the outside of your data model to the inside.

[marcelsmaglhaes · Accepted solution]

hey @KatTheDuck 

Maybe you can create a bridge table that contains three columns: EmployeeID, DepartmentID, and a flag indicating if the employee is a manager/deputy (True/False). Then you can create one-to-many relationships from the Employee table to the bridge table and from the Department table to the bridge table, and  you can set the RLS on the department ID in the bridge table where the manager/deputy flag is True.