Forum Discussion
USERNAME() returning weird e-mail format
Thanks for the tip on using gmail. I was able to test with the "live.com#[email protected]".
USERPRINCIPALNAME() returns the same "live.com#[email protected]" 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#[email protected]" 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.
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]
)
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.
Also I can see that it follows the same convention for each email address, so you could do this in the Power Query Editor.
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:
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]
)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.
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 [email protected] and now it shows [email protected]. I had to update my access table. Not sure of what circumstances determine which e-mail will be used.
- What is UN mean?
Does that means dynamic RLS (username() or userprincipalname() ) now works fine for both users - those with domain from organization and external domains too?
