Dynamic RLS with UserName()/email and multiple Cost Center Responsible

[Haleem]

I have managed to assign user access to the cost center report based on cost center responsible's email automatically using UserName() function. I maintain cost center responsible table [table name is UniqueCClist] in excel and upload it everytime there is a change. It works completely fine for the Cost center Responsible. But the problem is that how can I provide access to  [Country Responsible], [Regional Responsible], and [Group Responsible] based on their UserName()/email. I would like that every time - for example - DKSAHS login, he should have access to cost center 3001,3002,3003 and 3004 and so on. Please find below user tables and details and also LINK to the Excel file and PBI file https://1drv.ms/f/s!AtMKO3vGEsG5gR8MKw3S7Gg0hFod

Manage Role:

 

Table: UniqueCClist

Cost Center	Cost Center Responsible	Country Responsible	Regional Responsible	Group Responsible
3001	dksahs@chr-hansen.com	dksahs@chr-hansen.com	dkceo@chr-hansen.com	dkchariman@chr-hansen.com
3002	dkhjnn@chr-hansen.com	dksahs@chr-hansen.com	dkceo@chr-hansen.com	dkchariman@chr-hansen.com
3003	dknob@chr-hansen.com	dksahs@chr-hansen.com	dkceo@chr-hansen.com	dkchariman@chr-hansen.com
3004	dkceo@chr-hansen.com	dksahs@chr-hansen.com	dkceo@chr-hansen.com	dkchariman@chr-hansen.com

Table: Data Table

Cost Center          Cost

3001	1000
3002	1001
3001	1002
3001	1003
3001	1004
3003	1005
3003	1006
3004	1007
3004	678
3002	777
3003	888
3004	1003
3005	343
3006	444
3007	444

[adambhappy · Accepted solution]

Solution 1:
Unpivot the table with cost center and responsible so it will be one line per the combination of cost center+responsible (regardless of level).

Since it won't like the many-to-many relationship, keep the current table and add the unpivoted table as a new table and keep the current table as bridge table to maintain the one-to-many relationship. Don't forget to tick the box apply security filter in both directions.

Solution 2:
Change the RLS formula to include a OR statement

[Cost Center Responsible] =  UserName() || [Country Responsible] = UserName() || RegionalResponsible = UserName() || GroupResponsible = UserName() 

[Haleem · Accepted solution]

Hi Adam, Thanks a lot for a quick and specific response to my problem.

I liked solution 1 and have created unpivot table and use the existing table as a bridge between unpivot table and data table as shown in the picture. The problem is that I cannot apply "security filter in both directions" it's inactive, and this might be the reason that I cannot restrict the access.

[adambhappy · Accepted solution]

Solution 1:
Unpivot the table with cost center and responsible so it will be one line per the combination of cost center+responsible (regardless of level).

Since it won't like the many-to-many relationship, keep the current table and add the unpivoted table as a new table and keep the current table as bridge table to maintain the one-to-many relationship. Don't forget to tick the box apply security filter in both directions.

Solution 2:
Change the RLS formula to include a OR statement

[Cost Center Responsible] =  UserName() || [Country Responsible] = UserName() || RegionalResponsible = UserName() || GroupResponsible = UserName() 

[Haleem · Accepted solution]

Hi Adam, Thanks a lot for a quick and specific response to my problem.

I liked solution 1 and have created unpivot table and use the existing table as a bridge between unpivot table and data table as shown in the picture. The problem is that I cannot apply "security filter in both directions" it's inactive, and this might be the reason that I cannot restrict the access.

[Haleem · Accepted solution]

Please ignore my message above. I have fixed the problem. In order to have the option active for Security Filter in both Direction, i have to first select the "Both" option in the Cross Filter Direction as shown in the below screen shot.

[Haleem]

Hi Adam, Thanks a lot for a quick and specific response to my problem.

I liked solution 1 and have created unpivot table and use the existing table as a bridge between unpivot table and data table as shown in the picture. The problem is that I cannot apply "security filter in both directions" it's inactive, and this might be the reason that I cannot restrict the access.