RLS - Change Filter Condition Dynamically

[karun218]

Hello 

I have to apply the filter condition based on the user logged into the system

user table : user table has 2 columns user email , and filter condition to be applied on data set

User Name	Filter
k1@abc.com	'a','b','c'
k2@abc.com	'd','b','c'
k3@abc.com	'e','f','g','h','a'

Data table : data table has plant , catogery and amount columns

Plant	Catogery	Amount
a	cat1	100
a	cat2	20
b	cat1	123
b	cat2	145
c	cat1	100
c	cat2	167
f	cat1	456
f	cat2	555

my requirment is when user 1 logged into the system he should able to see a,b,c data

user 2 should see b,c data

user 3 should see f,a

hope i am clear with the question. please help me with the above scenario.

[karun218]

Hello Pragati

thanks for your email. I have gone through the link already but this will not work for my situation.

In the link, there is a common field between 2 tables and they joined each other to filter records for the user.

in my case we have more no of users(3000) and each user has different plants (200) plants. so i am looking how to apply filter for each user seperatly based on filter field.

[Pragati11]

HI @karun218 ,

What I think you should do is -

1. Transform your USER TABLE in such a way in query editor, that you end up with multiple rows for a user with plant values. Like User=k1@abc.com will end up with 3 rows for plant values a,b,c
2. Create a bridge table (PLANT Table) that has unique values for plants.
3. Create relationship of this PLANT table with your USER table and DATA table on "PLANT" column.

So, above 3 steps will give you required relationships for setting up your RLS.

Then write your DAX using for RLS.

https://radacad.com/what-do-you-need-to-implement-dynamic-row-level-security-in-power-bi

Thanks,

Pragati

[Pragati11]

HI @karun218 ,

There is a way to implement dynamic RLS in Power BI. Check following link if it helps:

https://radacad.com/dynamic-row-level-security-with-power-bi-made-simple

Thanks,

Pragati