Dynamic Filter in the RLS

[Pbi07]

Needed some help with this RLS filtering 

My table has the user emails along with Divisions that each user can view the data 

Location	email	Division 1	Division 2	Division 3	Division 4
EAST	Group1@abc.com	ABC Div	XYZ Div	123 Div
EAST	Group2@abc.com	ABC Div	XYZ Div	123 Div	NEW Div
SOUTH	Group3@abc.com	777 Div	555 Div

I unpivoted the above to make it easier. 

The Fact has data of GI's for divisions . Group1@abc.com should see only the qty of 50 for 3rd Oct. How can i filter in RLS for all various divisions based on email.

GI No	Date	Qty	Division
1000	1-Oct-21	25	XYZ Div
1001	3-Oct-21	30	ABC Div
1002	3-Oct-21	20	ABC Div
1003	15-Oct-21	70	555 Div
1004	15-Oct-21	50	123 Div
1005	20-Oct-21	100	NEW Div

How can i apply the RLS to handle this dynamically?  the pbix file is 

https://drive.google.com/file/d/1LKEIZmqAghp-El47AB_r0uWo0nWEbuCp/view?usp=sharing

[v-xiaoyan-msft · Accepted solution]

Hi @Pbi07 ,

Here is an article on how to use Dynamic RLS with detailed steps, please check if it will help you with your problem：

what-do-you-need-to-implement-dynamic-row-level-security-in-power-bi 

Hope it helps,

Community Support Team _ Caitlyn

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

[amitchandak]

@Pbi07 , Unpivot all these four columns

Division 1

Division 2

Division 3

Division 4

https://radacad.com/pivot-and-unpivot-with-power-bi

Rename attribute as Divisio

Join Division of table 1 with division of table2 (Filter direction - Bidirectional or From Table 1 to Table2)

Create RLS role and filter Table1 based on user principal name 

refer, if needed

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

[Pbi07]

Thanks @amitchandak . 

The issue is the Access Grp after unpivoting will have the below effect. This prevents from doing any joins on Division Name

Pbix -  https://drive.google.com/file/d/1LKEIZmqAghp-El47AB_r0uWo0nWEbuCp/view?usp=sharing