Forum Discussion

AndrewSEA's avatar
AndrewSEA
Advocate II
7 years ago
Solved

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@outlo...
Need Help
  • AndrewSEA's avatar
    AndrewSEA
    7 years ago

    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.

  • AndrewSEA's avatar
    AndrewSEA
    7 years ago

    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]
    )

GilbertQ's avatar
GilbertQ
Super User
7 years ago
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 [email protected] it will correctly resolve it with the USERPRINCIPALNAME() lookup
AndrewSEA's avatar
AndrewSEA
Advocate II
7 years ago

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.

  • GilbertQ's avatar
    GilbertQ
    Super User
    7 years ago
    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
    • AndrewSEA's avatar
      AndrewSEA
      Advocate II
      7 years ago

      Thanks for the tip on gmail.  I used that and I'm getting the "live.com#[email protected]" 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#[email protected]" in my look up table, so at least I have a workaround.  Do you know if it will always be live.com# prefix?  

    • AndrewSEA's avatar
      AndrewSEA
      Advocate II
      7 years ago

      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.

      • GilbertQ's avatar
        GilbertQ
        Super User
        7 years ago
        Well done on getting it working

        I would suggest using the USERPRINCIPALNAME to keep it consistent!