Row Level Security based on one table with 2 criteria from 2 different columns

[Tendo23]

Hello everybody,

I need your help, I am struggling with RLS.

I have on table which contains 3 columns:

Country	Product Group	Email
Germany	Product A	Boss@Boss.com
Germany	Product B	Boss@Boss.com
Germany	Product C	Boss@Boss.com
Germany	Product A	Worker@Boss.com
Germany	Product C	Worker@Boss.com

The table shows my rls structure. User Boss should see all revenues for every product of every country (Germany is just an expample, there are many more countries). Worker just is allowed to see the revenues for product A & C. RLS Table is connect by country to the revenue table.

I tried : [Email] = userprincipalname() && [Product Group] =userprincipalname()

Unfortunately it didn´t work out.

Does anybody has an idea how to solve my issue?

I am thankfull for every help I can get!

Tendo23

[vk_pbi]

Hi @Tendo23 

I think, you can add one more column to represent worker like (0,1), anyway Boss will have access to entire dataset.
Based on that, you can write your dax filter

I dont think so, it will work "[Product Group] =userprincipalname()" , 

Please provide some data to test it

Thanks

[Tendo23]

Hi @vk_pbi 

thanks for quick reply.

My expample file: TestDataFile 

What it should look like in the end:

User	Country	Revenues
Boss@Boss.com	France	3.615,00
Boss@Boss.com	Germany	59.508,61
Boss@Boss.com	Turkey	8.865,00
Boss@Boss.com (Total)		71.988,61
Worker@Boss.com	Germany	39.925,09

Thanks for your support!

Tendo23

[vk_pbi]

Hi @Tendo23 

A bit confused, please clarify

In Userstructure table, you have assigned product group as "Product A", "Product B" like wise whereas in Revenue table, you have product group as "A","B" like wise

Is this correct ?

[Tendo23]

Hi @vk_pbi ,

the table userstructure shows who is allowed to see the revenues in countries and revenues of product group.

User Boss@Boss.com can see everything.

User Worker@boss.com just can see the revenues for germany and only for the products A & C

Hope my explaniation helps. 🙂

[vk_pbi]

Just to work around.. I have created one more bridge table to hold product groups, then created a role for Worker, anyway boss will have access on entire dataset. Pleaes test below approach and let me know

Create role->filter on User Structure table [Email]="USERPRINCIPALNAME()"

Create filter on help_products ->[Product Group] IN
SELECTCOLUMNS(
FILTER(
Userstructure,
Userstructure[Email] = USERPRINCIPALNAME()
),
"@Product Group",[Product Group]
)

[Tendo23]

Hi @vk_pbi ,

thanks for your work. Unfortunately it doesn´t work.

I have recreated your model, but it doesnt work. 😞