Row Level Security Issue in SSAS tabular Model

Naami.Ayman 331 Reputation points
2021-08-02T14:52:08.92+00:00

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
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,249 questions
0 comments No comments
{count} votes

Accepted answer
  1. Naami.Ayman 331 Reputation points
    2021-08-04T09:57:04.097+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful