Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedJoin us at the 2025 Microsoft Fabric Community Conference. March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for $400 discount. Register now
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: "aaaa@email.com",
Manager:"ffff@email.com",
ITAnalyst: "mmmm@email.com"
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.