Standardize USERPRINCIPALNAME() returned value.

Requesting USERPRINCIPALNAME() function to return only the user email address for all users including external guest users. To implement the row level security in Power BI using the logged in user, we can use the USERPRINCIPALNAME() which should return the email address of the person in the Power BI service. For your internal users this shows the correct email address ([email protected]). For guest users (non internal users) defined in the Azure AD for which you want to serve these Power BI dashboards/reports (for example [email protected]), this USERPRINCIPALNAME() returns, live.com#[email protected]. This is by Power BI product design. In your Power BI data model, if you are using the regular email address to filter and apply row level security, you will see these two email addresses as it is ( [email protected] and [email protected]). When the external user ([email protected]) logs in to view the Power BI dashboards/reports, they will not see the data that they are supposed to see. Why? Because the USERPRINCIPALNAME() for the external users returns live.com#[email protected]. So the RLS does not match with what you have in the data model and cannot filter the records and return the appropriate ones that they can see. To make this work, you need to append live.com# in front of the email address when you bring in the data into the Power BI data model and use it for the external users. This is not obvious for the Power BI users and I am not able to find any reference in the Microsoft Power BI documentation online. Ask: USERPRINCIPALNAME() function to return the just the email address by removing the live.com# in Power BI service for external users and make it the same across the board.