how to manage role with mutiple values dynamic?

[Phoebe_Zhang]

hello,

if user A has company A and B access,

  user B has company C and E and X or any others access. 

  User C...

User D...

how to manger tole with mutiple values dynamic?

i have master table with company, and user and role. but how to filter company dynamic by login user account? 

please noted i understand how to manage it if user only has one company access . but there are some different company access..

would you please advise?

thank you.

[v-lid-msft]

Hi @Phoebe_Zhang ,

We can try create a user table which contain user's UPN, then apply userprincipalname() function in RLS rules of single roles to implete a dynamic RLS roles. Please refer to this document: https://docs.microsoft.com/en-us/power-bi/service-admin-rls#using-the-username-or-userprincipalname-...

1. user table with their UPN

2. relationship with fact table( you can also use user column or other)

3. use userprincipalname() function in rules

[User Email] = userprincipalname()

4. the result should be following:

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared？

By the way, PBIX file as attached.

Best regards,

[Phoebe_Zhang]

thanks CST，

i understand this, but some roles are by company level, some roles may be based on country level, and sales man may based on zone level. this is not fixed. will automaticlly updated by different user.

Not one company field is enough .

i donot kow how to automaticlly filter this.

[v-lid-msft]

Hi @Phoebe_Zhang ,

Does the role of users are dynamically changed in fact table? If the roles of user does not change frequently, we can create multi user table, such as UPN with Country, UPN with Zone, then create multi RLS roles using differenct user tables and assign users to different roles. If it does not meet your scenario, could you please provide some sample mockup data such as we making above?Please upload your files to OneDrive For Business and share the link here.

Please do not contain any confidential information or real data.

Best regards,

[bheepatel]

Hi @Phoebe_Zhang 

My suggestion would be to have a Table similar to the one below and make use of Relationships.

You can create a role for each user where you only allow the user access to its UserID. For example, you can create a role "UserID1" and you can use the following DAX expression: Table[UserID] = 1 so that the user can access all rows in the Table below where UserID = 1.

Since RLS remains active through relationships, you can then create a relationship between the table below and another table through the CompanyID field.

UserID	CompanyID
1	A
1	B
2	A
3	A
3	B
3	C

This method will also allow you to add and remove CompanyIDs per User quite easily.

Hope this helps!