Forum Discussion

gisa_sam's avatar
gisa_sam
Frequent Visitor
3 years ago
Solved

RLS - Multiple User Roles with no relationship

Hi,

 

I have 2 tables, a department table and a user table. Department table is one of the dimensional table connected to the main fact table.While User table is completely different table with no relationship to any other table. The level of access is provided based on User permission level and department mentioned in the User Table.

 

Consider below tables as an example:

Department Table

Team MemberDepartmentTeam
tm1dep1t11
tm2dep1t12
tm3dep2t21
tm4dep3t31

 

User Table

User EmailUser NameUser DepUser Role
[email protected]Aadep1Dep_role
[email protected]Bbt31Team_role
[email protected]CcexecExec_role
[email protected]DdfinanceAcc_role
[email protected]Eedep2Dep_role

 

I'm trying to figure out a way to provide role level security on the above tables based on following cases:

1. If user Aa logs in, he will be able to view only his department (dep1) and teams under him (t11, t12).

2. If user Bb logs in, he will be able to view only his team (t31).

3. If user Cc logs in, he will be able to view departments associated to him (dep1 and dep2).

4. If user Dd logs in, he will be able to view all of the departments in the table.

 

I used following table filter DAX expression for Case 1 and Case 2, by creating a virtual relationship between two tables by department column but this doesn't work for Case 3 and 4. Case 3 doesn't have any table to show association, hence an IF statement is applied to filter the association the expression.

 

[Department] IN CALCULATETABLE(
VALUES('User Table'[User Dep]),
FILTER(
ALL('User Table'),
[User Email] = USERPRINCIPALNAME()
&& [User Role] = "Dep_Role"
)

)

 

How can I get it sorted for Case 3 and 4.

The users are called in a single service group created, there can't be multiple groups for different roles as this was one of the requirement.

 

Could anyone please help me here?

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  gisa_sam 

    You can refer to the following dax 

    VAR _user_dep =
    CALCULATE (
        MAX ( 'User Table'[User Dep] ),
        'User Table'[User Email] = USERPRINCIPALNAME ()
    )
RETURN
    SWITCH (
        CALCULATE (
            MAX ( 'User Table'[User Role] ),
            'User Table'[User Email] = USERPRINCIPALNAME ()
        ),
        "Dep_role", IF ( [Department] = _user_dep, TRUE (), FALSE () ),
        "Team_role", IF ( [Team] = _user_dep, TRUE (), FALSE () ),
        "Exec_role", IF ( [Department] IN { "dep1", "dep2" }, TRUE (), FALSE () ),
        "Acc_role", TRUE ()
    )

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  gisa_sam 

    You can refer to the following dax 

    VAR _user_dep =
    CALCULATE (
        MAX ( 'User Table'[User Dep] ),
        'User Table'[User Email] = USERPRINCIPALNAME ()
    )
RETURN
    SWITCH (
        CALCULATE (
            MAX ( 'User Table'[User Role] ),
            'User Table'[User Email] = USERPRINCIPALNAME ()
        ),
        "Dep_role", IF ( [Department] = _user_dep, TRUE (), FALSE () ),
        "Team_role", IF ( [Team] = _user_dep, TRUE (), FALSE () ),
        "Exec_role", IF ( [Department] IN { "dep1", "dep2" }, TRUE (), FALSE () ),
        "Acc_role", TRUE ()
    )

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.