How to restrict data based on hierarchy

[POSPOS]

Hi All,

I have a requirement to implement row level security based on the below data.

Level 1 , Level 2 and Level 3 are the hierarchies.

Requirement: My requirement is to implement row level security in such a way that, if  Level 1 logs in, he should see all data. If anyone from Level 2 logs in, he should see their specific Level 3 data.

Eg:

If Level 1=SM1 logs in, then he should see all data i.e., M1,M2,M3, E1,E2.....E8.

If Levrel2 =M1 logs in , he should only see E1,E2,E3.

Can someone please advise on how to implement this using the sample data provided. Pbix file is attached here.

Thank you.

[Anonymous · Accepted solution]

Hi @POSPOS , 

Please use the following formula:

VAR _user =
    USERPRINCIPALNAME ()
VAR _is_l1 =
    _user IN VALUES ( 'Table'[Level 1] )
VAR _is_l2 =
    _user IN VALUES ( 'Table'[Level 2] )
VAR _is_l3 =
    _user IN VALUES ( 'Table'[Level 3] )
RETURN
    IF (
        _is_l3,
        'Table'[Level 3] = _user,
        IF ( _is_l2, 'Table'[Level 2] = _user, IF ( _is_l1, 'Table'[Level 1] = _user ) )
    )

Result:

Best Regards,
Zhu

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

[POSPOS]

@Anonymous  - Thank you for your detailed response. The solution worked. 🙂