Dynamic RLS: propagate to non-security dimensions

[Sumsar]

Hi. I have been struggling finding a good solution to a very common RLS-problem for awhile. I will try to explain it here with a simple example:

My data model contains the following tables:

Dimensions:

UserBridge (RLS) 

Company

Department

Project

Facts:

Sales

Budget

First I create a relation in the data model *:1 from UserBridge to Company setting the filter direction to both and apply security filter in both directions. UserBridge contains every valid combination between UserID and CompanyID. Then I relate Company to Sales and to Budget 1:*. Now I go through the RLS setup role etc. with filter on USERPRINCIPALNAME(). Then I relate the last two dimensions Project and Department to both Budget and Sales 1:*.

The security works perfectly, but when I create a slicer and view as user 1 (who can only see a subset of companies through RLS), I still see all projects and all departments, even though some of them does not exist for the user 1's companies. Now I know perfectly why this is happening, and it is not even a security breach, as it is only for convenience of the single user, that I want to only show a subset of departments/projects.

I tried to change all the relations between Department and Project to filter in both directions, which would technically solve the problem, but is not a best practice solution. Furthermore this workaround quickly rasies ambiguity issues in the model, when having more than 1 fact tables/ related dimensions.

Next I could try to contain Department and Project in the Company table, making the company table explode, as the same department can exist in multiple companies, same being the case for Projects.

Last option I can think of is using the department and project fields directly from the Budget and Sales fact tables, but this is not good practice either, as I would have to filter two slicers every time I need to select a department/project.

I suppose this is a fairly common case / data model, so what am I missing here? I am not looking for more workarounds, but rather what is the best practice here.

Thanks.

/Rasmus   

[Sumsar]

Hi vasathi,

Yes I have, 

1. Create this measure - in my case I have 4 fact tables connected to the same dimension table so I have 4 COUNTROWS statements:

SlicerFilterFinance =

COUNTROWS(ActualIncome) +

COUNTROWS(ActualBalance) +

COUNTROWS(BudgetIncome) +

COUNTROWS(BudgetBalance)

2. Then put it in visual level filters for all relevant dimension table slicers like this:

3. Now the dimension table slicers only show the values if represented by >= 1 rows in any of the fact tables in the measure.

Simple, but effective 🙂

Hope this helps.

Br   

[Sumsar · Accepted solution]

The answer was very simple after all... This piece of beauty did the trick:

[CustomerID] =
CALCULATE (
MAX ( User[CustomerID] ),
FILTER ( User, User[UserEmail] = USERPRINCIPALNAME () )
)

This code needs to be applied to all Dimensions in my security role for which I only want to see lines relevant to Customer XXX

Hope this can help others too. 

[Sumsar]

@amitchandak 

I was unable to find the solution to my problem in the links you provided. I have tried to illustrate the problem here, using another example, that hopefully is easier to relate to (this model works as expected, dynamically filtering Company table to only show allowed companies for UserEmail XXX, but it currently shows ALL Products):

Any help is much appreciated.

@sturlaws I see you have solved similar issues in the past, maybe you have a suggestion?

I can produce a sample file if needed.

/S

[kumarprabhu815]

Hey, if you dont mind can you please share the final pbix file with solution

[amitchandak]

@Sumsar , refer if this can help

RLS - Row Level security
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns...
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
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://blog.tallan.com/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/
https://radacad.com/what-do-you-need-to-implement-dynamic-row-level-security-in-power-bi

[Sumsar]

Sorry for the long reply-time. I think I am on the right track now. I am pretty sure this can be achieved by applying some filter to the "non"-security dimensions. As they share the CompanyId field with the Company table, I assume that something like this filter in the security role would do the trick, beware of pseudo-code:

(NB, User has a physical relation to Company).

Manage Roles:

User ->

Email = USERPRINCIPALNAME()

Department ->

CompanyId = ... TREATAS(VALUES(Company[CompanyId]),Department[CompanyId]) ...

TREATAS because I want to simulate a physical relationship that is not possible between Company and Department tables.

I just can't get the DAX to work...

[vasathi]

Dear SumSar

Have you find any solutions.  I'm also having same requirement.  

Request you to share that file and it will help others like me.

Regards