Row Level Security Issue in SSAS tabular Model

Question

Hi,

I have a request form end users to implement in row level security, which is to allo Team managers to see historical data about their team members, not only when they are linked to them.
Example:
Team Manager: TM
Employee: Emp

TM1 (emp1,emp2,emp3) from 01/01/2021 to 31/01/2021
TM2 (emp2,emp4,emp5) from 01/02/2021 to 31/12/2999

The request is that TM2 needs to see data for emp2 when he was with TM1

I implemented this rule for the fact tables as follows:

1- I created this table:

user_security(team_manager,tm_email,emp,validto)

2 - I have put this condition in dax filter for the fact table AA(emp,team_manager,date,measure1):

=VAR AccessRule = CALCULATETABLE('user_security', LOWER('user_security'[tm_email]) = LOWER(USERPRINCIPALNAME()))

RETURN (

CONTAINS(AccessRule,'user_security'[emp],'AA'[emp])

)

This code allows team managers to see all data for employees that were in their team, not only past and present data, but also future data with other team managers.
I want to add a condition on date (Validto) to filter the fact table, but I do not know how to put it in that code

Thank you for your help

Answer 1

Hi,

I solved the issue using this code in the dax filter :

=VAR AccessRule = CALCULATETABLE('user_security', LOWER('user_security'[tm_email]) = LOWER(USERPRINCIPALNAME()))

RETURN (

if(PRODUCTX(AccessRule,IF([emp]='AA'[emp],1)*IF('AA'[date]<=[validto],1)),TRUE())
)

Regards,
Ayman