Dynamic RLS with multiple dimension tables

[Hippo]

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

[koushikcs09]

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]

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]

Can you post your data model?

[amitchandak · Accepted solution]

@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

[Hippo]

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. 

[sarava0483]

Would you please share this solutio file, if possible