RLS for Multiple Roles

[Anonymous]

Hi experts,

I have 2 dimension tables region_mas and team_mas and an rls file where it has data of individuals' respective team and region names. I had two challenges in this project where individuals belong to multiple teams and regions, my client previously used Qlik sense so there in rls they can input a value ** for individuals having access to all teams or regions and they want to implement the same thing in Powerbi. I achieved this by creating two calculated tables named rls region and rls team.

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

rls region =

var aa = SUMMARIZE(rls,[USER ID],[rEGION])

var A = FILTER(aa,[REGION]="**")

VAR B = FILTER(VALUES(Region_mas[REGION_NAME]),NOT(ISBLANK([REGION_NAME])))

VAR C = CROSSJOIN(A,B)

VAR D = SUMMARIZE(C,[USER ID],[rEGION_NAME])

var E = FILTER(aa,[REGION]<>"**")

VAR F = UNION(D,E)

RETURN

f

rls team =

var aa = SUMMARIZE(rls,[USER ID],[Rls_TEAM])

var A = FILTER(aa,[Rls_TEAM]="**")

VAR B = FILTER(VALUES(Team_mas[Team]),NOT(ISBLANK([Team])))

VAR C = CROSSJOIN(A,B)

VAR D = SUMMARIZE(C,[USER ID],[Team])

var E = FILTER(aa,[Rls_TEAM]<>"**")

VAR F = UNION(D,E)

RETURN

f

For rls role the used dax are as below,

[REGION_NAME] = LOOKUPVALUE('rls region'[REGION_name],'rls region'[USER ID],USERPRINCIPALNAME(),'rls region'[REGION_name],Region_mas[REGION_NAME])

[TEAM] = LOOKUPVALUE('rls team'[TEAM],'rls team'[USER ID],USERPRINCIPALNAME(),'rls team'[TEAM],Team_mas[TEAM])

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

The above process works fine, But I have 2 questions.

1. Is there any way to achieve this with Dax without creating those calculated tables.

2. Even though the above process works fine I have a problem here, Let's take an individual a@s.com where he belongs to 2 teams and 2 regions, here I want to see team CNS data of Ernakulam only but it shows for both Chennai and Eranakulam regions.

a@s.com	CHANNEL SALES	Ernakulam
a@s.com	CHANNEL SALES	Chennai
a@s.com	CNS	Ernakulam

Please use the below drive link for the excel files and pbix used.

https://drive.google.com/drive/folders/1XbZgu07goVMzn6Kj8soK2bCP_Xzayhf2?usp=sharing 

Attached Screenshots

Regards,

Akash M

[v-xiaotang]

Hi @Anonymous 

Thanks for reaching out to us.

For the second question, I have tested it before, if a user has multiple roles, then the user can get all permissions belonging to those roles. For the first question, I need to take some time to confirm.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Tang,

Thanks for ur reply, so as u say for the second question is there any solution or way to achieve it. If yes Pls guide me.

Regards,

Akash Mohan