RLS with overriding userprincipal dax

[userdata]

Hi all,

I need to create a user which is a manager for RLS based on three fields.  ID, customers and type. What the rls needs to do is, based on the id which is userprincipal it has to filter out the type and customers that are applicable and then it needs to show all fields that the selected user can see without the id, so show also ids from others who can see the same customers or type.

As below example, we have Bart as an id. So if Bart logs in he only should see in customers fruit, banana or retail, but based on the userprincipalname knowing what he is allowed to see the dynamic rls should now show all the customers and types he can see independent from id

Now he has to be able to see also other ids who have the same customer and type as him : meaning now he can also see elena and kraus as an id. I tried to configure it based on userprincipalname to find out which fields Bart should see like banana, fruit and retail and then it needs to override the id and filter only the fields he was allowed to see as he is a manager. Anyone ideas how to set up that rls? Many thanks!

You can find the link to the pbix in here : https://github.com/userdata21/test12

[vapid128]

emmmmmmmmmm,

you need a table contains 2 colnums

1 user id , 2 power bi log-in email

link this table to whatever you get, both way.

modeling --> manage role -->new table

[Email]=username()

if somehow you can't make it both way, and it many to one then one to many

like below

we can use code:

[A_ID] in SELECTCOLUMNS(
FILTER('member','member'[members_area.email] = username()),
"areas"
,'member'[members_area.a_id])

[userdata]

Thanks, I tried but it doesnt work because it just filters based on user but I need to filter based on user to find out the brand and customer and then it has to list all the brands and customers it can see.

The rls should filters out like below, that should be the end solution but it only filters on bart?

[v-chenwuz-msft · Accepted solution]

Hi @userdata ,

Please try this expression to do that.

[customers] 
in
CALCULATETABLE (
    VALUES ( 'Table'[customers] ),
    FILTER ( ALL ( 'Table' ), [id] = USERNAME () )
)

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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