RLS - Multiple User Roles with no relationship

[gisa_sam]

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 Member	Department	Team
tm1	dep1	t11
tm2	dep1	t12
tm3	dep2	t21
tm4	dep3	t31

User Table

User Email	User Name	User Dep	User Role
aaaa@gmail.com	Aa	dep1	Dep_role
bbbb@gmail.com	Bb	t31	Team_role
cccc@gmail.com	Cc	exec	Exec_role
dddd@gmail.com	Dd	finance	Acc_role
eeee@gmail.com	Ee	dep2	Dep_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?

[gisa_sam]

Thanks, this worked!

[v-xinruzhu-msft · Accepted solution]

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.