Forum Discussion
RLS using aduser name
Hi ,
I'm bringing data in power bi using direct query, my table has aduser name. Can I use aduser for rls?
I checked username() function and it is resolving to userprincipal name in power bi service as [email protected]
Any help or idea on this?
If it is not working in the service, create a mapping table that links AD usernames to UPNs. Use USERNAME() in the service to retrieve the UPN, and apply a DAX lookup (LOOKUPVALUE) to map it to the corresponding AD username for RLS filtering.
Hi DiKi-I ,
we can also do thses steps Update your data source to store the full UPNs (e.g., [email protected]) in the ADUser field so that it directly matches what Power BI returns using USERNAME().
If that’s not possible, use a DAX expression in your RLS rule to extract just the username from USERNAME(), ensuring compatibility across both Desktop and Service.
You can use the following DAX to extract the short username from USERNAME()
Table[ADUser] =
VAR _user = USERNAME()
RETURN
IF(
FIND("@", _user, 1, 0) > 0,
LEFT(_user, FIND("@", _user) - 1),
_user
)Regards
Chaithanya.
11 Replies
Hi DiKi-I Yes, you can use AD usernames for RLS in Power BI if the column matches the USERNAME() format ([email protected]).
Create an RLS role with a filter like [aduser] = USERNAME().
Test using "View as Role" in Power BI Desktop.
Publish the report and assign roles in the dataset settings.
I tested like this in power bi desktop and I'm passing aduser in the role, this is working. But when I'm checking username () in card in service it is giving me [email protected]. I don't have access to test with real data yet in service.
In case if it doesn't work is there a way to lookup the upn using aduser in power bi?If it is not working in the service, create a mapping table that links AD usernames to UPNs. Use USERNAME() in the service to retrieve the UPN, and apply a DAX lookup (LOOKUPVALUE) to map it to the corresponding AD username for RLS filtering.
Hi DiKi-I ,
we can also do thses steps Update your data source to store the full UPNs (e.g., [email protected]) in the ADUser field so that it directly matches what Power BI returns using USERNAME().
If that’s not possible, use a DAX expression in your RLS rule to extract just the username from USERNAME(), ensuring compatibility across both Desktop and Service.
You can use the following DAX to extract the short username from USERNAME()
Table[ADUser] =
VAR _user = USERNAME()
RETURN
IF(
FIND("@", _user, 1, 0) > 0,
LEFT(_user, FIND("@", _user) - 1),
_user
)Regards
Chaithanya.
Thanks for your response. I asked the source to provide me email in the table. As username() is returning upn with domain in service .
my ad user in table doesn;t have domain, it is just the adusername.
Hi DiKi-I,
If possible can you share the sample pbix file with sample data to check the data so that we can give the solution.
Regards
Chaithanya.
Hi DiKi-I ,
May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
Regards,Chaithanya
Hi DiKi-I ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya.
Hi @DiKi-I ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya.
Hi @DiKi-I ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya.
