Dynamic RLS with External User (Azure B2B) help

[rossnruthie]

I am working on setting up external sharing of a report and running into a problem.

Scenario:

• Create a report that uses dynamic RLS based on external email
• Share a PBI report with an external user username@yahoo.com
• verify that RLS filters data specific to username@yahoo.com

I've invited and the user username@yahoo.com has accepted the invitation.  I've added them to a role that i've set up:

The username@yahoo.com maps to the dealerGroupUser.UserEmail which then filters to a dealer group.

I deployed the report, added the external user to the Dealer Group role and shared the report.  When the external user opens the report it's blank.  

I created a calculated measure to display the user name and found that USERPRINCIPALNAME() which i would expect to return 'username@yahoo.com' is actually returning 'live.com#username@yahoo.com'.  For some reason it is appending 'live.com#'.  I'm assuming it's because it's an external account and that AAD is creating a microsoft live account behind the scenes as denoted in the guest user properties:

I couldn't find anything in the documentation about this.  Does this always append live.com#?  What if I invite someone from another AAD tenant, will it append something else or nothing at all?  This makes it difficult to map the guest email to the userEmail value in my dynamic RLS table.

Can someone help me out?

Thanks!

[GilbertQ]

Hi there

What I would suggest first doing, is if you change the email address to match with the "#Live" does it then work?

When I have done it in the past it always worked with simply putting in the User's email address that was external.

When going to another tenant it also makes changes for the UPN, but I still use the actual email address and it works.

[rossnruthie · Accepted solution]

I actually found an MSDN post where apparently under the hood AAD appends that value to the user name for external B2B customers and there is no way to prevent it.  So in my case in order to get RLS to map to an external account (with the appended value) to the email address stored in the db table i changed the DAX to remove the "live#" value and everything worked fine after that.

I should note that the external users in this case do not map to another tenant.  They were plain email address like gmail.com.  This may explain why the B2B tenant to tenant worked without modification in your case but not with me.

[Anonymous]

@rossnruthie : i have the same case . can you please share the dax formula to remove live.com# .

Is  live.com# is constant across all other emails ? or will it change based on domains?

[rossnruthie]

Hi @Anonymous 

I used the SUBSTITUTE function in the DAX filter in the Role to replace the unwanted text with a blank space "".  

Substitute Function - DAX

We have two types of external customers; those with generic external emails such as gmail/yahoo, etc and those with their own Azure Active Directory with O365.  I found that only the external customers with generic accounts have the "live.com#" appended and the Azure Active directory accounts remained unchanged.  This makes sense because when sending invites to generic email accounts users create a microsoft guest account the first time they attempt to access power bi via an invite while the AAD accounts don't have to as they already exist in a different tenant.

[Anonymous]

@rossnruthie : Thanks for your reply

TRIM(SUBSTITUTE(USERPRINCIPALNAME(),"live.com#",""))  is the formuala i used and without trim it was not not working intially . After adding trim it was working fine . It's the same with you as well?