question

NaamiAyman-5203 avatar image
0 Votes"
NaamiAyman-5203 asked NaamiAyman-5203 answered

Row Level Security Issue in SSAS tabular Model

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

sql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

NaamiAyman-5203 avatar image
1 Vote"
NaamiAyman-5203 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.