Strange prefix on USERNAME() and USERPRINCIPALNAME() for external users

[MalJan19]

We are working on exposing Power BI reports to external users, so I am setting up RLS on the reports using USERNAME(). When sharing with an external test email address, I discovered that Power BI tacks live.com# in front of the username, causing RLS not to work as expected. The same thing happened with one other test email address, but no prefix was added on a third test email address. The same thing happens with USERPRINCIPALNAME().

I have a workaround, but I still have a couple questions:

1. Is there a way to prevent the prefix from getting added on?

2. Are there any other prefixes that could appear that I need to be aware of?

Thanks in advance for the help!

[Rose__123]

Hi @MalJan19, did you solve the problem? 🙂 

/Katrine

[Gabriel_Walkman]

@MalJan19 my current theory is that external users without a company account will get the live# prefix, but with external users from another organization, the format is more complex. Ie. fake.ferguson@fakeorg.com would be something like fake.ferguson_fakeorg.com#EXT#@yourorg0.onmicrosoft.com.

There was an older post with a dax solution that supposedly works for both cases, but I haven't tried it yet:
https://community.powerbi.com/t5/Service/USERNAME-returning-weird-e-mail-format/td-p/545032/page/2

[Greg_Deckler]

Right, this is part of the just in time provisioning of external users. The prefix is where their login is coming from. Ensures uniqueness and all that. You should expect a prefix for any supported external federated login. I think outlook.com, live.com probably some others.

[MalJan19]

Thanks Greg. Will the email always be preceded by a #? In other words, can I build a statement into RLS that removes anything before #?