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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Hippo
Frequent Visitor

Dynamic RLS with multiple dimension tables

Hi,

 

I have a simple data model where I need to apply Row Level Security. The Model consist of two dimension tables, Customer and Product, and one Fact table, Sales. I added a User security table to dynamically filter the Customer dimension data based on the username and this succesfully filters out the most important thing as well, the Sales Fact table. The join between the User security table and the Customer dimension is Many to Many with "Both" as Cross filter direction and security is applied. Works perfectly.

 

Now I have a list of thousands of products that have an owner and I would like to use the same User Security table to similarily filter the data in Product Dimension table instead of the user having to search his/her products from the dimension table. When I create a similar relationship between User security table and the Product dimension table, I can't make it active as "Power BI Desktop allows only one filtering path between tables in Data Model". And I can't deactivate the other relationship as it's already doing the filtering.

 

Is there a good solution to apply this kind of dynamic filtering to multiple dimension tables with just one User Security table? I really don't want to create more than one of these security tables.

 

The model and the solution I have is similar to what Guyinacube used in this video https://www.youtube.com/watch?v=9wN33rTaiB4 but I just need to filter that other dimension table as well.

 

Thanks

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Hippo , refer if this can help

https://blog.tallan.com/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/

https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns...

https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
https://community.powerbi.com/t5/Desktop/DAX-Code-for-Dynamic-RLS/m-p/807960

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

6 REPLIES 6
koushikcs09
New Member

Maintaining data security has become a crucial requirement for businesses today, especially when dealing with BI reporting. With the increasing number of roles involved in data access, managing row-level security (RLS) has become a complex task. In order to provide secure and filtered data access to each role, businesses need to establish a set of filtering dimensions that would be applied to each role. These dimensions could range from profit centers to sales organizations or even trader/buyer codes. The challenge is to create a system that effectively manages these dimensions for each role and ensures their automatic application when users log into each report. Therefore, the question arises - how can businesses create an efficient RLS system that provides secure and filtered data access to each role, without compromising on the performance of BI reporting?

Hippo
Frequent Visitor

Hi,

 

Thanks for your responses and I think I got this solved. 

 

I did create a Reference query to UserCompany table and used that to filter out the Customer dimension but then I had two UserCompany tables and I still would need to create only one filter to filter out more that one owner and I don't have an organizational hierarchy to use.

 

I ended up using the code in the below link 

'Sales'[Company]
    IN CALCULATETABLE (
        VALUES ( 'Sheet2'[Company] ),
        FILTER ( 'Sheet2', 'Sheet2'[userId] = USERNAME () )
    )

https://community.powerbi.com/t5/Desktop/DAX-Code-for-Dynamic-RLS/m-p/807960 

 

Worked like a charm when added this with my tables and columns to Manage Roles -> DimCustomer table

 

Thanks again for the help.

Greg_Deckler
Super User
Super User

Can you post your data model?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@Hippo , refer if this can help

https://blog.tallan.com/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/

https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns...

https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
https://community.powerbi.com/t5/Desktop/DAX-Code-for-Dynamic-RLS/m-p/807960

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi,

 

Thanks for your help. I ended up using this piece of DAX to filter out the other dimensions in "Manage role" Security module.

 

Link to source:

https://community.powerbi.com/t5/Desktop/DAX-Code-for-Dynamic-RLS/m-p/807960 

 

'Sales'[Company]
    IN CALCULATETABLE (
        VALUES ( 'Sheet2'[Company] ),
        FILTER ( 'Sheet2', 'Sheet2'[userId] = USERNAME () )
    )

Works perfectly after changing my tables and columns over there. I did try to create a reference query from my security table and linked that to the Customer table (M2M) and it did work but this solution is much nicer. 

Would you please share this solutio file, if possible

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.