Dynamic RLS role creation based on Login name

[Charu]

Hi Everyone,

Based on the user logged in for them their own record and the person who is reporting to the logged in user those details also should display 

How to compare the logged in username in the Manage role creation?

For example:

Employee_ID | Employee_Name | Reporting_TO_Name | Email_address

1                     | John                       |   Ajay Kumar                | [email protected]

2                     | William                   |  Ajay Kumar                |[email protected]

3                     | Ajay                        |                                     |[email protected]

If Ajay logs in then he can able to see all the records based on Reporting_TO_Name, here the Reporting_TO_Name Ajaykumar both belong to [email protected]

when ajay logs into power bi service in the profile tab on right corner his name display as Ajay Kumar and email id is [email protected]

I used the roles as 

[Email_address] = USERPRINCIPALNAME() ||
[Reporting_TO_Name] & "@domain.com" = USERPRINCIPALNAME()

I want to compare the Reporting to name with the display name once user logins.

Please Help me to get the solution on this.

Thanks 

[Anonymous · Accepted solution]

@Charu,

In your scenario, create two roles, one using the DAX below. In Power BI Service, add Ajay as member of this role.

1=1

Another using DAX below.

[Email_address] = USERPRINCIPALNAME()

Regards,
Lydia

[Charu]

Hi @Anonymous

I'm already doing the way you suggested, I thought there is no other option so I tried the same like you shared .

Thank you so much for your support. 

[Ara93]

Hi how did you solve the problem even i am facing the same issue kindly share the resolution. i am using direct query.

thanks,

Aravindan M

[Anonymous]

@Charu,

Create a new column in your table using DAX below.

is manager = IF(Table[Reporting_To_Name]="",1,0)

Create the role using DAX below.

If(MaxX(
Filter(
Table,
Table[Email_address]=Username())
,Table[is manager])=0,
Table[Email_address]=Username(),
1=1
)

Reference:
http://radacad.com/dynamic-row-level-security-with-manager-level-access-in-power-bi

Regards,
Lydia

[Charu]

HI @Anonymous

I'm getting the following error message as 

Function 'MAXX' is not allowed as part of the row level security expression on DirectQuery models