RLS - External User ID format

[Jaydo44]

Hello, dears,

I need your support with RLS's various external user ID formats.
Indeed, I can see the following with only a few cases. When implementing the RLS in the PBI desktop, using the userprincipalname() function in the dax formula, I figured out that this function returns various cases (I speak only about external users😞

• Either "firstname.lastname@domain.com"
• Or "live.com#firstname.lastname@domain.com"
• Or "firstname.lastname_domain.com#EXT#@company.onmicrosoft.com"

I based the filter on what we get from the Azure AD, which is not working because of the three cases already experienced (maybe there are more).

My question is simple:

How to deal with it?

Does it depend on how the invitation is sent to the external user from the Azure AD? For example, does it depend on the fact that the external id has a Microsoft account? Does it depend only on the external user domain (Gmail, Yahoo, etc.)?

I know that the question is quite broad, but maybe your experience can help, and I thank you in advance.

[Jaydo44]

Hi Sheng,

Actually, I continued the investigation, alone and with Microsoft support. I think I'm close to the solution.

My first worries was to identify the different format of User ID. 
It seems that the prefix may vary, and the suffix (with #EXT#) is only used to test role in PBI Service.
But whatever the context, the best would be to remove the prefix or the suffix.

So I'm working on a new RLS dax formula that would extract either the left part of the User ID to remove the prefix (when the ext user logs in with such a prefix for instance), or to remove the right side of the User ID to test role in PBI service. 
For the other cases (to test the role with "view as" in PBI desktop, you can enter whatever you want).
The formula would look like:

[user] = RIGHT(SUBSTITUTE(userprincipalname(),"#","-"),LEN(SUBSTITUTE(userprincipalname(),"#","-"))- SEARCH("-", SUBSTITUTE(userprincipalname(),"#","-") ,1,0))||[user] = userprincipalname()|| [user] = SUBSTITUTE(PATHITEM(SUBSTITUTE(USERPRINCIPALNAME(),"#","|"),1),"_","@").

I think we can accept this answer as solution for this question.

[Anonymous]

Hi @Jaydo44,

Perhaps you can add some if statement to check if current account is an external account(the account include #EXT#), then you can remove the right part(#EXT#@company.onmicrosoft.com) and replace domain part string(_domain.com) to the normal domain(@domain.com) to apply RLS filter.

Regards,

Xiaoxin Sheng