Dynamic Use of USERPRINCIPALNAME() for Dynamic RLS

[Anonymous]

Introduction
There are scenarios where username or userprincipalname required to use to give access but to use USERPRINCIPALNAME() dynamically and come with the different possibility.

SCENARIOS:

Scenario 1 : In any Organisation each employee belongs to different department and  have access as per his designation. An Executive always have access to all data which is related to his employee and a Manager to his department but not to the data which belongs to Executive and similarly Lower Level employee will have access to his data only.

Access Definition:
Access definition

Scenario 2. multiple users exists in the same row.(Additional Example how we can use the USERPRINCIPALNAME( ))

Steps to follow to solve above scenarios

STEP 1
Create or Import your user table and Fact table and make sure there must not be any relationship between the user table and fact table .

STEP 2
Create a Flag column in Fact tables. In this case created a flag in Charged Hours.

STEP 3

Create RLS with Role User and use below codes respectively for respective Tables.
For Users use below dax: 

[email]=USERPRINCIPALNAME()

For Charged Hours Use below code:

[FLAG] = SWITCH(
CALCULATE(FIRSTNONBLANK('Users'[Designation],1),Users[Email]=Lower(USERPRINCIPALNAME())),
BLANK(),0,
"Executive",1,
"Manager",
IF(
'Charged Hours'[Department] IN
SELECTCOLUMNS(FILTER(Users,Users[Email]=Lower(USERPRINCIPALNAME()) && 'Users'[Department]<>BLANK()),"DEP",Users[Department])&&  'Charged Hours'[Designation]<>"Executive"
,1,0),
"ITAnalyst",
IF(
'Charged Hours'[EmpId] IN
SELECTCOLUMNS(FILTER(Users,Users[Email]=Lower(USERPRINCIPALNAME())),"EmpId",Users[EmpId])
&& 'Charged Hours'[Designation]<>"Executive"
,1,0),0)

 

Step 4
Now Check for your Final RLS using the Modelling tab with view as option
Executive: "[email protected]",
Manager:"[email protected]",
ITAnalyst: "[email protected]"

Note: For More information or the details please refer to pbix.

Scenario 2 : Follow the steps till 2 on Step 3
Create Role as MultipleId
and Apply on Fact Table using the code :

[Flag] = SWITCH(TRUE(),[EmpID] IN
SELECTCOLUMNS(Filter(User2,IF(
FIND(USERPRINCIPALNAME(),[email],1,0)>=1,TRUE(),FALSE())),"EmpID",[EmpID]) ,1,0)

RLS applied, 
view as Role:

Note: Please Refer to the PBIXs for more details.

Dynmic_RLS_Part1.pbix

Dynamic_RLS_Part 2.pbix