RLS - Give access to all data in one specified table, ignoring RLS rules

[CatoD]

Hi,

Problem: 

There is already working RLS solution based on dimension with departments (eg. User X has access to Department D.01 etc.)

The report contains many KPI's, I need to give the user access to one of the KPI, to which his department doesn't have access.

The data for this KPI is in separated fact table. 

I could just give this user X access, to data from other departments, but that's not the solution i'm looking for.

The expected result is, that the user X has access to all the data for his department, and all the data for the KPI 'ABC' (which is available in other departments).

[v-stephen-msft]

Hi @CatoD ,

Everyone's insights are great. I just want to confirm that if you have solved it.

Please mark the helpful replies as solutions, so that more people will benefit from it.

Thanks.

Best Regards,

Stephen Tao

[amitchandak · Accepted solution]

@CatoD , Please find the file attached. You need small modifications to have other values. You can do that by subtracting the values Non All from All

I have done this item. Duplicated Item as Item New( You need to use all columns, I used one for column), then I created relation between RLS -> Item ALL -> Item. 

Hide the Item

and used only Item all in visual 

[CatoD]

Thank you, your idea solved my issue.

So I did the following:
Since all fact tables in my data model consist of a column 'kpiName', I had to just create a technical dimension 'dRLS_KPI' with 2 columns: idKPI and kpiName.
The dimension should have all KPI names in the data model. Create a one-to-many relation with the fact tables.
Create another technical table ('dRLS_USERPRINCIPALNAME') with 2 columns: idKPI and userprincipalname.
There should be specified IDs of the KPIs the user should have access to and the USERPRINCIPALNAME.
Create a relation with the dimension 'dRLS_KPI'.
Last step is to create a viewing role, which filters 'dRLS_USERPRINCIPALNAME'.
Add the user to the new role.

Now the RLS works both ways: the user has access to the whole fact table for specified KPIs in 'dRLS_KPI' as well as for RLS created for the 'department' dimension.

[Darui6]

Hi,

I suppose the department table also has a relation to that other fact table, otherwise there wouldnt be a problem.

So you could create a second copy of that fact table, and based on that copied table you can calculate the measure. Make sure to NOT connect that copied table. The the RLS should apply to the original table, but not to the copied one. You can create a copy as a calculated table like this:

Table_Copy = Addcolumns('OriginalTable', add here the columns you need)

[CatoD]

Thanks for an answer

Unfortunately it won't work in my situation, because, the data in fact table has to be connected to dimension of department.

So if visualisation of fact table uses column from dimension 'department', which is connected to RLS table, it won't work.