Manage Roles.

[Anonymous]

Hi,

I have a scenario where in I need to  manage role based on the Country.

Given Data - 

Name	Role	Country	TotalAmount
xyz@xyz.com	Sales	France	1000
abc@xyz.com	Account	Italy	2000
def@xyz.com	SL	France	3000
ghi@xyz.com	SL	France	4000
xyz@xyz.com	Sales	Spain	5000
abb@xyz.com	SL	Spain	6000

What needs to be achieved when user "xyz@xyz.com" logs in - 

[Sales User Role]  -> if logged in user role ="Sales" then read the Country Name, and now show all the users under these selected countries.

expected output when "xyz@xyz.com" logs in (must show all the users from France and Spain because "xyz@xyz.com" is linked to France and Spain only) -

Name	Role	Country	TotalAmount
xyz@xyz.com	Sales	France	1000
def@xyz.com	SL	France	3000
ghi@xyz.com	SL	France	4000
xyz@xyz.com	Sales	Spain	5000
abb@xyz.com	SL	Spain	6000

Thanks.

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous, 

You can create a RLS role with filter defined below: 

Search('Table'[Country],CONCATENATEX(FILTER('Table','Table'[Role]="Sales"),'Table'[Country],","),1,0)>0

Best Regards,
Qiuyun Yu

[Anonymous]

Hi @v-qiuyu-msft,

This is exactly what I have been looking for. 

Thanks a lot for quick response.

[Anonymous]

@v-qiuyu-msft,

I have an extended scenario where in now we need to check if a user is either SALES or ACCOUNT then show all rows wd the countries these two users have.

using the below dax, it is showing the data for the countries which are linked to Role = ACCOUNT and SALES when the SALES User logs in.

I want to show only Sales User countries and not Account user countries when Sales logs in n vice versa.

Also, there can be multiple Sales and Account users. but my requirement is to filter on countries of the logged in users only.

ex - sales user 1 - France and Spain 

sales user 2 - Greece and Egypt.

Account user - Italy

so if Sales user 1 logs in, it must show all records for France and Spain only or

 if Account user logs in, it must show all records for Italy only.

SEARCH(Table[Country],CONCATENATEX(FILTER(Table,Table[Role]="Account"||Table[Role]="Sales"),Table[Country],","),1,0)>0

Given data - 

Name	Role	Country	TotalAmount
xyz@xyz.com	Sales	France	1000
abc@xyz.com	Account	Italy	2000
def@xyz.com	SL	France	3000
ghi@xyz.com	SL	France	4000
xyz@xyz.com	Sales	Spain	5000
abb@xyz.com	SL	Spain	6000
abc.11@xyz.com	SS	Italy	2000

Expected, when Sales user logs in ( xyz@xyz.com) -

Name	Role	Country	TotalAmount
xyz@xyz.com	Sales	France	1000
def@xyz.com	SL	France	3000
ghi@xyz.com	SL	France	4000
xyz@xyz.com	Sales	Spain	5000
abb@xyz.com	SL	Spain	6000

Expected, when Account user logs in ( abc@xyz.com) -

Name	Role	Country	TotalAmount
abc@xyz.com	Account	Italy	2000
abc.11@xyz.com	SS	Italy	2000

Thanks.