cancel
Showing results for 
Search instead for 
Did you mean: 

Dynamic Use of USERPRINCIPALNAME() for Dynamic RLS


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 definitionAccess definition

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

 

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 .
Rss.PNG

 

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

 

CreatingFlag.png

 

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)

 

 

 

 

 

 RLS_Step1.pngRLS_Step2.png

Step 4
Now Check for your Final RLS using the Modelling tab with view as option
Executive: "aaaa@email.com",
Manager:"ffff@email.com",
ITAnalyst: "mmmm@email.com"
ExecutiveView.pngItAnalyst.pngManage.png


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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)

 

 

RLS2.png

 

 

RLS applied, 
view as Role:

 

 

RLS2.1.png

 

Note: Please Refer to the PBIXs for more details.