Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.