Complex Dynamic Row Level Security

[Anonymous]

Hello PowerBI Community,

I have a requirement to configure Dynamic Row Level Security.

Below is the scenario:

We have 2 dimension tables for RLS and multiple Facts.

The Fact table contains columns for Market and Function.

Dim_Markets :

1. NAM
2. LAM
3. Asia

Dim_Functions:

1. Operations
2. Finance
3. Sales
-----------------------------------------------
I need to configure RLS at 3 Levels:

1. Global User
-- Should have access to all the markets for all the functions.

2. Market User
-- Should have access to a specific market for all the functions.

3. Head Of Market & Function. (i am finding this tricky)
-- Should have access to all the markets for one specific function (known beforehand).
-- Also, He/She will have access to all other functions for one market (known beforehand).
-- e.g: If I am the Head of Market for 'LAM' and 'Operations',
   then i should be able to access
   1. 'Operations' data for all the markets
   2. 'LAM' data for functions other than 'Operations'

I would really appreciate if someone can assist me here.

Thanks.

[Anonymous]

Hi @amitchandak ,

Thanks for the quick response.

But i also need the RLS at Function level. I am finding the third scenario that i have mentioned a bit difficult.

3. Head Of Market & Function. (i am finding this tricky)
-- Should have access to all the markets for one specific function (known beforehand).
-- Also, He/She will have access to all other functions for one market (known beforehand).
-- e.g: If I am the Head of Market for 'LAM' and 'Operations',
   then i should be able to access
   1. 'Operations' data for all the markets
   2. 'LAM' data for functions other than 'Operations'

Thanks.

[amitchandak]

@Anonymous , You need to have table like

My advice would be to have table like that have Many to many bi-directional join with Region

Region	Email
NAM	a@abc.com
LAM	a@abc.com
Asia	a@abc.com
NAM	b@abc.com
LAM	c@abc.com
Asia	d@abc.com

a is global and other are regional user

How to use Row Level Security (RLS): https://youtu.be/NfdIA0uS6Nk