Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Dynamic RLS with combination of multiple dimensions

Hello everyone,   I want to apply dynamic RLS, but on a combination of two dimensions. For example, I have a Sales FactTable that contains sales data. It also has a DimDepartmentId & a DimCountryId...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
5 years ago

I think I have found a solution myself. I created a new table UserAccessDepCountry. This table contains the Usernames and the combination of the Department & Country they are allowed to see.

In the data model, I did not create a relationship between this table and the Department & Country table. So the data model currently looks like below:

 

For the dynamic RLS, I created the following role:

([DimDepartmentId], [DimCountryId]) IN SELECTCOLUMNS(
FILTER('UserAccessDepCountry', ('UserAccessDepCountry'[UserName] = USERPRINCIPALNAME())), "DimDepartmentId", [DimDepartmentId], "DimCountryId", [DimCountryId])

 

 

This works as I wanted, as it filters on the combination Department-Country, without the need of a new column in the Sales table. However, my question is if this is a valid solution. Will I face performance issues because I don't use relationships, but DAX to filter the Sales table?

MariaJoe's avatar
MariaJoe
Frequent Visitor
3 years ago

Hi, I am having similar scenario. Was there any performance issue when you use dax filter on Fact table?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi MariaJoe , the performance was good in our case, so we used this solution. However, it will probably vary from case to case.

    • MariaJoe's avatar
      MariaJoe
      Frequent Visitor
      3 years ago
      Thanks!