Dynamic RLS With Multiple Domains

[cdizzle22]

Good morning,

There is a dashboard that will be accessed from people accross different domains. I would like to set up dynamic RLS so there only has to be one security group in AAD. I have seen other posts showing to create a name table but then when the RLS Dax expression is created, it is using the full email address determine what RLS role should be used. Does anyone have tips on how to set up Dynamic RLS only using the domain portion of the email address and not the whole email address?

Thanks!

[jdbuchanan71 · Accepted solution]

Not quite, no.

When a user logs into PowerBI they use their email address as their username.

This is what gets returnd by the DAX function USERPRINCIPALNAME().

Lets say bgates@microsoft.com is logging into your report.

This portion of the code:

VAR _UPN = USERPRINCIPALNAME()
VAR _Domain = RIGHT(_UPN,LEN(_UPN)-(FIND("@",_UPN,,0)))
RETURN _Domain

Would return 'microsoft.com'.  

I am assuming your model has a colmn that has the domain in it and that is where you apply the filter in the role.

[cdizzle22]

I do not have the column created yet but this explanation helps clear up some of the questions. Thanks for your help!

[cdizzle22]

Thanks for the help and this looks like what I need but want to make sure I understand it correctly.

This is going to seperate the domain from the email address so there will still need to be a table created that shows email addresses. Do you have to set the table up as company name in one column, email in another column, and then create a relationship with the company name in another table to make sure the data is filtered correctly?

[jdbuchanan71]

@cdizzle22 

You can get the domain of the signed in user and apply it as a filter over the table that has the domains.

[Domain] = 
VAR _UPN = USERPRINCIPALNAME()
VAR _Domain = RIGHT(_UPN,LEN(_UPN)-(FIND("@",_UPN,,0)))
RETURN _Domain