Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
gisa_sam
Frequent Visitor

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
aaaa@gmail.comAadep1Dep_role
bbbb@gmail.comBbt31Team_role
cccc@gmail.comCcexecExec_role
dddd@gmail.comDdfinanceAcc_role
eeee@gmail.comEedep2Dep_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?

 

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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.

 

View solution in original post

2 REPLIES 2
gisa_sam
Frequent Visitor

Thanks, this worked!

v-xinruzhu-msft
Community Support
Community Support

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.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.