Row Level security Filter over 4 tables

[jimmy7377]

Hello friends and follow Power Bioneers

Im a relative Noob compared to alot of you so please go easy on me.

I am having some (I play done the some) issues with getting my RLS to work see below my Dax I have currently i have enclosed the Dump of my Tables realtionships, I cannot get it to filter accross the 4 tables, I have had to put in a roles table as it was creating a Many to many relationship from Users to Areas ( I can hear my Database trainer saying NO!) to created Roles table to force a Many to 1.

Relationships are as follows

Users table, User_Role to Roles table, Role

Roles table Role to Areas table, Area role

Areas Table Committee_Name to Wards table Committee name

Wards table, Postcode to Clients Table, Post code

[Committee_name] in SELECTCOLUMNS(
filter(
'Areas',
related('Users'[Email])= USERPRINCIPALNAME()
),

"Committee_Name", [Committee_Name]
)

One my dax is not working it tells me The Column'Users[Email]' either doesn't exists ot doesnt have a relationship to any table availble in the current context.

I have been round and round trying to get the filter to work. 

I hope i have enclosed enough for you to understand my issue, if not please let m eknow and I will be happy to add more.

Kindest regards,

Jimmy

[mwegener · Accepted solution]

Hi @jimmy7377 ,

try this.

[Role] IN SELECTCOLUMNS( FILTER(Users, Users[Email] = USERPRINCIPALNAME()), "Role", Users[User_Role])

[jimmy7377]

@mwegener  Thank you kindly for your help on this.

It is telling me Column 'Role' Cannot be found or may not be used in this expression Refering to the first [Role] Move it to Committee Name it then accepted it but when applied would not show any Committee _Name and would not filter Area_role or Role.

[mwegener]

Hi @jimmy7377 ,

you have to set the table filter on the table Roles.

[jimmy7377]

Hi @mwegener

ah i had it on the Wards table.

I moves it to the Role but still getting the same :

[mwegener]

Hi @jimmy7377 ,

the screenshot shows the Users table not the Roles table...

[jimmy7377]

See complete plonker.

thakn you so much, that seems to be working on testing im going to test further.

thankyou a thousand times

Jimmy

[mwegener]

Hey @jimmy7377, you're welcome. 😉