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 am working on setting up external sharing of a report and running into a problem.
Scenario:
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!
Solved! Go to 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.
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.
@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?
Hi @Anonymous
I used the SUBSTITUTE function in the DAX filter in the Role to replace the unwanted text with a blank space "".
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.
@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?
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.