How to make sure RLS will propagate always?

Question

I have the below model:

And I am unsure if my RLS propagates correctly…

 

The model has 2 regular facts FactRevenue and FactSales  (I cut them from the screenshot, just for clarity) and it also has Aggregated Revenue (shown in the screenshot).

 

I have two roles, on Employee Current where I have the next RLS:

ROLE1:

=[NT Username] = USERNAME()

 

ROLE2:

=[TeamLeadID] IN SELECTCOLUMNS(FILTER('Employee Current','Employee Current'[NT UserName] = USERNAME()),"ID",[ID])

 

(Most users should see only themselves, and Team leads themselves + their teams they lead).

 

Engagement Role is an UNPIVOT of Employee (each Employee can have up to 5 different roles…) (Therefore it is 1:*)

 

 

My questions are:

• will my RLS filter in Employe Current flow to Engagement Role and then flow to WBS, and then flow to FactSales ?
• will my RLS filter in Employe Current flow to AgProfitCenter and then flow to Aggregated Revenue?

Answer 1

RLS uses a value for earch record. It does not propagates as such.

you should be creating one role. And it should use a table where Employees and leads are combined in hierarchical structure. And then use LOOKUPVALUE DAX to dynamically get desired results based on connection.

e.g. DAX

=DimEmployeeType[EmployeeTypeKey]=LOOKUPVALUE(DimEmployeeTypeHierarchy[EmployeeTypeID], DimEmployeeTypeHierarchy[UserName], USERNAME(), DimEmployeeTypeHierarchy[EmployeeTypeID], DimEmployeeType[EmployeeTypeKey])