cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate II
Advocate II

USERNAME() returning weird e-mail format

I'm using USERNAME() to check against a security table for Row Level Security (RLS) based on the user.  

 

For one of the users, I'm getting a weird e-mail format returned:  live.com#john.doe@outlook.com

Didn't figure this out until I explicitly put USERNAME() in a card on the report to see what was being used.

 

Has anyone seen that?  Is this going to happen for all users that have outlook.com e-mail addresses?  

 

Using the "live.com#john.doe@outlook.com" in my look up table doesn't work, so I'm thinking I have to filter out any text before a # in an e-mail address to correct for this weird behavior.  Any insights to this would help me with configuring my access table for others.

2 ACCEPTED SOLUTIONS

Thanks for the tip on using gmail.  I was able to test with the "live.com#john.doe@gmail.com".

 

USERPRINCIPALNAME() returns the same "live.com#john.doe@gmail.com" as USERNAME().  I saw some online documentation saying the same, that USERNAME() behaves differently on the PowerBI Service than on Desktop, but is the same as USERPRINCIPALNAME().

 

It looks like my table entry of "live.com#john.doe@gmail.com" does work though, so that is a workaround if needed, but very clunky.  So what I did was strip that Azure prefix out before the compare to get it to work.  Here's the code I use to limit the Country view:

 

[Reseller_Country] =
   LOOKUPVALUE(
      ReportUsers[Country],
         ReportUsers[Microsoft Account],
         MID(USERNAME(),
         FIND ("#", USERNAME(), 1,0) + 1,
         LEN (USERNAME())- FIND("#", USERNAME(), 1,0)
         ),
         ReportUsers[Country],
         [Reseller_Country]
      )

 

Now I don't have to worry about any random prefixes being added to the username e-mail address by Azure.  Thanks for the help.

View solution in original post

I guess you can't put e-mails into the message body.  I just posted a reply that disappeared.  Sucks.

 

So basically, I've determined there are 3 different e-mail formats that get return: 1) regular, 2) live.com# prefix, and 3) abc_workmail.com#EXT#(at)onmail.mail.com

 

It's a pain to figure out which one is going to show up, so I decided to just fix the e-mail back to regular and not have to mess with the special characters inserted.  Here's an example of my formula used in the RLS role:

 

[Region] =
VAR findpound = FIND("#", USERPRINCIPALNAME(), 1, 0)
VAR CleanUserName = MID(USERPRINCIPALNAME(),
    FIND ("#", USERPRINCIPALNAME(), 1, 0) + 1,
          LEN (USERPRINCIPALNAME()) - findpound)
VAR CleanedUserName = IF(LEFT(CleanUserName,4)="EXT#",
    SUBSTITUTE(LEFT(USERPRINCIPALNAME(),
         findpound-1), "_","@"),CleanUserName)
RETURN
LOOKUPVALUE(
   Region[Region],
   Region[Country],
   LOOKUPVALUE(
      ReportUsers[Country],
      ReportUsers[Account],
      CleanedUserName),
   Region[Region],
   [Region]
)

View solution in original post

19 REPLIES 19
Super User II
Super User II

Hi there

The reason that it appears that way is because that is how the display name is stored in Azure Active Directory.

I would suggest changing it to USERPRINCIPALNAME() which is for the Power BI Service.
USERNAME() is for when you are inside a domain.

And finally in your lookup table store just the email address john.doe@outlook.com it will correctly resolve it with the USERPRINCIPALNAME() lookup




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Thanks, I'll try USERPRINCIPALNAME() and see if that works.  Need the user with that e-mail to verify.  Looks like you can't emulate it using Test as Role on the PowerBI Service.

Hi there,

If you have a gmail or hotmail address you could test it yourself.

You are correct in that the test a role doesn't work




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Thanks for the tip on using gmail.  I was able to test with the "live.com#john.doe@gmail.com".

 

USERPRINCIPALNAME() returns the same "live.com#john.doe@gmail.com" as USERNAME().  I saw some online documentation saying the same, that USERNAME() behaves differently on the PowerBI Service than on Desktop, but is the same as USERPRINCIPALNAME().

 

It looks like my table entry of "live.com#john.doe@gmail.com" does work though, so that is a workaround if needed, but very clunky.  So what I did was strip that Azure prefix out before the compare to get it to work.  Here's the code I use to limit the Country view:

 

[Reseller_Country] =
   LOOKUPVALUE(
      ReportUsers[Country],
         ReportUsers[Microsoft Account],
         MID(USERNAME(),
         FIND ("#", USERNAME(), 1,0) + 1,
         LEN (USERNAME())- FIND("#", USERNAME(), 1,0)
         ),
         ReportUsers[Country],
         [Reseller_Country]
      )

 

Now I don't have to worry about any random prefixes being added to the username e-mail address by Azure.  Thanks for the help.

View solution in original post

Well done on getting it working

I would suggest using the USERPRINCIPALNAME to keep it consistent!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

True, using USERPRINCIPALNAME() lets me test e-mail addresses on Desktop as well.  Thanks.

Yeah happy to assist




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Well now I'm getting another variant from other users.  

 

It's returning "johndoe_abccompany.com#EXT#@microsoft.onmicrosoft.com" where the user's e-mail is johndoe@abccompany.com

 

Seems random on which format to use.  Frustrating to design something that works for everyone.  Perhaps the USERPRINCIPALNAME() function needs to be fixed.

Hi there

have you test it, where in your dataset you have just the email address?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Yes, that's what I've been using.  So what I've done now is replicated the Security Role and use the unaltered USERPRINCIPALNAME() value and put that weird e-mail format into the table to accomodate when that happens.

I have the exact same issue. It only started Monday of this week. Prior to that the Username() function did return the email address of the person signed in. A solution to this other than just having to change my file to fit the random UPNs that I get is much needed. 

I would suggest always using the USERPRINCIPALNAME() as that is what is designed for the Power BI Service.

Also I can see that it follows the same convention for each email address, so you could do this in the Power Query Editor.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

I have tried both and the problem is that neither function returns the correct output. An even bigger problem is that they used to. 

 

From Microsoft's own documentation the show dynamic row level security working as such:

Capture.PNG

 

 

You can see by design, neither function is supposed to return the extra information, allowing you to set something up in rls like [useremail] = UPN(). But that no longer works because of the need to manipulate the email address in the table, since you can't manipulate the UPN without compromising logins. 

 

You could do as OP said and just enter the extra information into your table, but now the old users that are in there behave differently. Their function still returns their email while the newly added users return the extra information. So to keep everyone the same you would need to remove everyone and reinvite so every user UPN() function returns the extra information and then write up the dax to fix the emails to match the UPN() function. 

 

 

I guess you can't put e-mails into the message body.  I just posted a reply that disappeared.  Sucks.

 

So basically, I've determined there are 3 different e-mail formats that get return: 1) regular, 2) live.com# prefix, and 3) abc_workmail.com#EXT#(at)onmail.mail.com

 

It's a pain to figure out which one is going to show up, so I decided to just fix the e-mail back to regular and not have to mess with the special characters inserted.  Here's an example of my formula used in the RLS role:

 

[Region] =
VAR findpound = FIND("#", USERPRINCIPALNAME(), 1, 0)
VAR CleanUserName = MID(USERPRINCIPALNAME(),
    FIND ("#", USERPRINCIPALNAME(), 1, 0) + 1,
          LEN (USERPRINCIPALNAME()) - findpound)
VAR CleanedUserName = IF(LEFT(CleanUserName,4)="EXT#",
    SUBSTITUTE(LEFT(USERPRINCIPALNAME(),
         findpound-1), "_","@"),CleanUserName)
RETURN
LOOKUPVALUE(
   Region[Region],
   Region[Country],
   LOOKUPVALUE(
      ReportUsers[Country],
      ReportUsers[Account],
      CleanedUserName),
   Region[Region],
   [Region]
)

View solution in original post

After a couple of weeks speaking with Microsoft reps, the issue seems to be resolved. The UPN() and UN() function should now return the email of the user rather than the added extension for external users. 

What is UN mean?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Yes, it looks like they did change the e-mails to show actual e-mails.  Interestingly, one of my external users had an e-mail that was aliasa@company.com and now it shows FirstName.LastName@company.com.  I had to update my access table.  Not sure of what circumstances determine which e-mail will be used.

Ok thanks for letting me know





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Thanks for the tip on gmail.  I used that and I'm getting the "live.com#john.doe@gmail.com" coming through using USERPRINCIPALNAME() as well.  It looks like USERNAME() and USERPRINCIPALNAME() work the same on the PowerBI Service according to the documents I found.

 

On the bright side, the user came back and has access now since I put the "live.com#john.doe@outlook.com" in my look up table, so at least I have a workaround.  Do you know if it will always be live.com# prefix?  

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors