Forum Discussion
USERNAME() function DAX
I'm trying to use either =username() or =principleusername() with RLS. My issue is that I have created a dataset as a SQL View and brought this into the desktop, I then created a tbl and imported from Excel with the users name and email address as this didn't exist in my original View. This is then joined using the users name.
I then apply the roles in the desktop and test and all works perfect, as soon as I publish up to the PBI service the RLS doesn't work anymore. I have a fairly good understanding of RLS and have it working for regions without an issue.
I guess this maybe something to do with the AD but unfortunately that's well past my understanding! Can anyone advise what I'm doing wrong pls?
Thanks in advance PBI peeps Toby
Hi Tucker,
According to the official document, the format of user name in Service is UPN, which looks like an email address [email protected]. So,
1. The list of user names should be UPN, which we can find out the exact format in the Service with function userprincipalname().
2. We should use userprincipalname() in the role we defined in Desktop.
>>Within the Power BI service, username() and userprincipalname() will both return the user's User Principal Name (UPN). This looks similar to an email address.
My test:
formula of a role: IF ( USERPRINCIPALNAME () = "[email protected]", [Column1] < 1000, [Column1] = 0 )Best Regards!
Dale
6 Replies
Hi Tucker,
According to the official document, the format of user name in Service is UPN, which looks like an email address [email protected]. So,
1. The list of user names should be UPN, which we can find out the exact format in the Service with function userprincipalname().
2. We should use userprincipalname() in the role we defined in Desktop.
>>Within the Power BI service, username() and userprincipalname() will both return the user's User Principal Name (UPN). This looks similar to an email address.
My test:
formula of a role: IF ( USERPRINCIPALNAME () = "[email protected]", [Column1] < 1000, [Column1] = 0 )Best Regards!
Dale
Hi Dale,
Thanks for you reply, I have got this far but after playing all day yesterday but still have an issue.
So in my data I have the email address (UPN) of the user so have created a role and said [eMailAddress] = username() in the desktop. Published up to my Workspace and in Security selected "Test as Role" and I see exactly what I'd expect.
The issue then comes when I create a Content Pack and share with a O365 group (having added the user names to the RLS in the dataset), I then go into the group and "Get Data" when I then view the report in the group I can see everything.
I have read that the users need to be "Read Only" in the group which they are but I'm the Admin so don't know if that makes a difference. I got a collegue to test and he see nothing just blank reports.
Thanks again for your reply and if you know what I'm doing wrong please let me know cause I've been at this for weeks!!
Cheers Toby
Hi Toby,
I wonder if you have published an App. If so, everyone has to get a role even no filter applied to him/her. This is from my test. Have a try please.
Best Regards!
Dale
