Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Haleem
Helper III
Helper III

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

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:

Role-DAX EXPRESSION.png 

 

 

Table: UniqueCClist

Cost CenterCost Center ResponsibleCountry ResponsibleRegional ResponsibleGroup Responsible
3001dksahs@chr-hansen.comdksahs@chr-hansen.comdkceo@chr-hansen.comdkchariman@chr-hansen.com
3002dkhjnn@chr-hansen.comdksahs@chr-hansen.comdkceo@chr-hansen.comdkchariman@chr-hansen.com
3003dknob@chr-hansen.comdksahs@chr-hansen.comdkceo@chr-hansen.comdkchariman@chr-hansen.com
3004dkceo@chr-hansen.comdksahs@chr-hansen.comdkceo@chr-hansen.comdkchariman@chr-hansen.com

 

Table: Data Table

 

Cost Center          Cost

30011000
30021001
30011002
30011003
30011004
30031005
30031006
30041007
3004678
3002777
3003888
30041003
3005343
3006444
3007444

 

 

3 ACCEPTED SOLUTIONS
adambhappy
Resolver II
Resolver II

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() 

 

View solution in original post

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. unpivot table bridge.png

 

Access role dAX NEW.png

View solution in original post

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.access DAX role new new.png

View solution in original post

4 REPLIES 4
adambhappy
Resolver II
Resolver II

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() 

 

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. unpivot table bridge.png

 

Access role dAX NEW.png

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.access DAX role new new.png

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. unpivot table bridge.png

 

Access role dAX NEW.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.