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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors