Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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
Solved! Go to Solution.
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.
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.
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.