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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
robinHH
Frequent Visitor

Many-to-Many Relationships for RLS in Power BI Desktop for Report Server / On-Premises

Dear Power BI Community,

 

I have a data model in which each user (principal-username) may have several sub_ids (e.g. business units). The CEO for example has access to all business units, hence, his principal-username matches several sub_ids.  In the fact sales table each transaction has a sub_id.

 

screenshot1.PNG

 

Now I aim that when a user logs in, Power BI recognizes his principal username and he sees only the transactions that are in his sub_id. However, since Power BI Desktop for Report Server seems not to allow many-to-many relationships, I have absolutly no clue how to implement RLS.

 

I appreaciate any kind of your help.

 

Cheers
Rob

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

You can only do RLS with a many to many relationship if you have a single search direction in the data model between these two tables. What you probably want to do is add a parent dimension table where each user is only listed once.

 

Another alternative is to have rules/roles that work off the sub_id, and an external reference table (similar to your "USERS"  example) that governs membership to these roles.

View solution in original post

RodrigoMachado
Frequent Visitor

Hi Rob, that is right, Power BI Report Server does not support many to many relantionship, so you can do the many to many relantionship in Power BI Desktop and upload it to Report Server, but when you try to download it back to you, let's suppose you want to change something in the dashboard, you won't be able to do it in Report Server, thus you will have to open the file in PBI Desktop and at this point you won't be working in the file that is hosted in PBI Report Server anymore, meaning you will have to create a new link for the users if you upload that again to Report Server.

 

So my personal conclusion to this is that the best way to create these many to many relantionships is by creating a bridge table, which does the magic of connecting tables creating the same result as a many to many. You can find more about this technique searching on google. Here are some sources that might be helpful:

https://www.youtube.com/watch?v=Sge_g9hTXWE

 

https://www.youtube.com/watch?v=l6hR2Me6Ang

https://www.seerinteractive.com/blog/join-many-many-power-bi/

 

Let me know if you were able to work things around, I am currently working with this too so I am interested in solutions.

 

If this was helpful, please mark it as an accepted solution.

View solution in original post

2 REPLIES 2
RodrigoMachado
Frequent Visitor

Hi Rob, that is right, Power BI Report Server does not support many to many relantionship, so you can do the many to many relantionship in Power BI Desktop and upload it to Report Server, but when you try to download it back to you, let's suppose you want to change something in the dashboard, you won't be able to do it in Report Server, thus you will have to open the file in PBI Desktop and at this point you won't be working in the file that is hosted in PBI Report Server anymore, meaning you will have to create a new link for the users if you upload that again to Report Server.

 

So my personal conclusion to this is that the best way to create these many to many relantionships is by creating a bridge table, which does the magic of connecting tables creating the same result as a many to many. You can find more about this technique searching on google. Here are some sources that might be helpful:

https://www.youtube.com/watch?v=Sge_g9hTXWE

 

https://www.youtube.com/watch?v=l6hR2Me6Ang

https://www.seerinteractive.com/blog/join-many-many-power-bi/

 

Let me know if you were able to work things around, I am currently working with this too so I am interested in solutions.

 

If this was helpful, please mark it as an accepted solution.

lbendlin
Super User
Super User

You can only do RLS with a many to many relationship if you have a single search direction in the data model between these two tables. What you probably want to do is add a parent dimension table where each user is only listed once.

 

Another alternative is to have rules/roles that work off the sub_id, and an external reference table (similar to your "USERS"  example) that governs membership to these roles.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors