Dynamic RLS with combination of multiple dimensions

[jasperp]

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. For the dynamic RLS, I want that a certain user only can see the data of the IT department of Belgium, and the data of the R&D department of France (So not both IT & R&D for both France & Belgium).

I currently found a solution, by creating a table OrganizationalStructure. This table contains all combinations of Country & Department. I then added a column in the Sales table 'DimOrgStructureId', referencing to a row in the OrganizationalStructure table. Finally, I created a table that maps the Users with the DimOrgStructureIds of which they should have access to. 

My question is, is there a better way to do is? Especially since I do not want to add the DimOrgStructureId to the Sales table. Ideally, the sales table only contains the sales information and the information about the DimCountryId & DimDepartmentId.

Thanks a lot in advance.

[v-yangliu-msft]

Hi  @jasperp  ,

You can use the external tool Dax studio or the Performance analyzer on Power bi Desktop to test your method to check the overall running time, resource consumption time, etc., to make judgments.

This is the installation and use of Dax Studio:

https://daxstudio.org/

https://www.sqlbi.com/articles/capturing-power-bi-queries-using-dax-studio/

This is the relevant content of the Performance analyzer:

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-performance-analyzer

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jasperp]

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]

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

[jasperp]

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

[MariaJoe]

Thanks!