Dynamic Row Level Security-to show all country for few users otherwise respective country for others

[Anonymous]

Hello Everyone,

I want to show all country data for few users using dynamic row level security otherwise few user have access to few countries or some have visibility to single country, which country they belong to. Those users who need to access all countries have "All" value in country name in user table. Can someone help me, how to show full data for Amit, and Priyank user and limited data for other users as per their countries.

I've three tables, Users, Country and Sales.

User table:

Name	Email	Country
Prateek	prateek@abc.com	India
Prateek	prateek@abc.com	Thailand
Amit	amit@abc.com	All
Vicky	vicky@abc.com	China
Vicky	vicky@abc.com	Thailand
Priyank	priyank@abc.com	All

Country Table:

Country

India

China

Thailand

Sales Table:

Product	Sales	Country
A	1000	India
B	2000	China
C	2500	Thailand
D	3000	India
E	500	China

Can someone provide me the best approach to solve this scenario??

Thanks in advance.

[HGINEV]

Hello,

I have a similar problem. I have countries France, Germany and UK. I want to set roles for users from respective country (e.g. Role to be like this [Country] == "France") and people who are from France to see only France data. 

However I want the same France users to see some reports which are related to data for the three countries.

For example we have 1 report which shows a card with measure count(sales_id) =>for France they are 100. But on the second card report I have count(sales_id),RemoveFilters(Sales[Country]) =>here we see the sales for all countries which are 300. When I apply the RLS for France, automatically the second report displays 100 instead of 300.

Thank you in advance for your help!

[negi007]

@Anonymous Yes, since RLS is filtering data from your master user table, if it does not find any matching value then no data will be visiable. I am happy that I was able to solve your problem. RLS can sometime be difficult to implement as well. Cheers!!!

[Anonymous]

Hi @negi007 

I got another issue in RLS. After applying these 2 roles as suggested by you, I published the report. Then I added one user in Region Role for test purpose but when I click on 'test as role' for this role, it shows blank data for users.

Then I try for Top role and the output is same.

It is showing no data for both roles. In Power BI Desktop, it is working properly for both roles but after publishing in Power BI Service it's not working. Could you please help?

Thanks in advance.

[negi007]

@Anonymous After implementing RLS in your dataset in powerbi desktop, you have to add RLS in powerbi services as well. You will have to add all IDs in the role. For example, let say you have a region role, then add all ids who will have region access and same way add ids in the other roles.

[Anonymous]

Hi @negi007 

I added those ids in Power BI Service under RLS but it is not showing data for users.

[negi007]

@Anonymous can you pl. share your powerbi file if possible you can keep non-sensitive data.

[Anonymous]

Hi @negi007 

It is having confidential data and also in client space. So can't send you but I replicated the same Dax expressions in roles and created table as per your suggestion.

Even for userprincipalname() Dax function for region, its not showing data for same users in Power BI Service 

[negi007 · Accepted solution]

@Anonymous  Hi Prateek, you will have to do some changes in your user table. I suggest you to have your table in below format. Create a seperate column for user who can have access to full data.

you will then need to create a relatioship like below

then two roles, like below

1st for regional role

2nd for all india role

then you can do the testing like below

I am also attaching pbix file for your reference.

[Anonymous]

Thanks @negi007. It helped me but found one issue, in User table if some country is not avaialable (for eg. if I don't have any user for Thailand country) and it is on Country master table then for Roles on All countries it's showing only those countires which are matching with Coutry master table.

Anyways, I've user for all countries so no issues with the solution.

Thank you so much!!