Power BI Manage Roles - Single User Multiple Instances within the same Role

[VayunRangnekar]

Hello All!

I've got a problem when setting up roles where I was trying to base it on the User's email and what store's data they can see. 

I've looked around and can't seem to get an answer so hoping someone can help 🙂

The Email table looks like this:

Store	Email
A	A1
B	B1
C	A1
D	C1
E	D1

The issue is that all measures break when I filter to the "A1" email due to multiple stores being brought back within the roles. I've tried using this:

VAR _User = USERPRINCIPALNAME ()
VAR _Stores = CALCULATE ( VALUES ( 'Email'[Store] ), FILTER ( EmailTable, 'EmailTable'[Email] = _User )
RETURN
'Stores'[Store] = _Stores

With no luck when I select an email with multiple stores within the Email Table.

[danextian · Accepted solution]

hi @VayunRangnekar ,

Have your tried creating a many to many relationship (role filtering store dimension) and then in the role set [Store] = USERPRINCIPALNAME()? I have the same setup in one of my clients. An account email may actually repeat several times as one can have multiple assigned territories.

[VayunRangnekar]

Perfect! That has resolved the issue.

I didn't have the Many-To-Many relationship as you mentioned