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

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.

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.