Row Level Security - access based on Job Role

[bigrods]

Hi All,

I wondered if someone could please help: I am creating a report of staff data that has a table of absences that lists both annual leave and sickness; I'd like to restrict the Sickness data in there to people with certain job roles.

I have 3 tables: Staff, Assignment & Absence

Staff is all staff details, with email address

Assignment is the job role details for each staff member (a staff member could be seconded etc so may have multiple entries in here)

Absence lists all absences (annual leave, sickness etc).

I'd like it so that: Only Staff members with the Job Role of "Board Level Director" or "Chief Executive" can see everything in the Absence table (Annual Leave and Sickness), whereas everyone else can only see Annual Leave data. I've included the email address as it'd be something to do with userprincipalname() but not sure quite how to implement it.

In the example below, Linda, Susie and Sharon would be able to view all 4 records in the Absence table, but Jim would only be able to view the Annual Leave rows because of his "Dentist" job role.

Any thoughts or help would be gratefully received!

Staff

PersonID	Name	Email
2919834	Linda	linda@job.com
21289404	Susie	susie@job.com
2920048	Sharon	sharon@job.com
16339670	Jim	jim@job.com

Assignment

AssignmentID	PersonID	Job Role
123456	2919834	Chief Executive
456789	21289404	Board Level Director
987654	2920048	Board Level Director
654321	16339670	Dentist

Absence

AbsenceID	PersonID	AbsenceType	Days Lost
784512	258369	Sickness	1
326598	968574	Annual Leave	3
876521	238511	Sickness	5
653239	258369	Annual Leave	2

[v-yalanwu-msft · Accepted solution]

Hi, @bigrods ;

You could create a [Key] column in 'staff ' and 'Absence' table ; then create a relationship.

1.create a key column in 'staff ' table by dax.

Key = IF( RELATED(Assignment[Job Role]) in { "Board Level Director","Chief Executive"},"A","B")

2.create a key column in 'Absence ' table by dax.

3.create a relationship.

Then use USERPRINCIPALNAME() to implement Rls.

the final show:

If I am Jim:

If i am Lina 

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bigrods · Accepted solution]

@v-yalanwu-msft 

I have managed to get this working; I've had a play around and joined the Staff and Assignment table together to make StaffAssignment.

I've then duplicated the Absence table and filtered the copy just by Sickness data to make a "Sickness" table. I then joined this to the StaffAssignment table by the Key field on both tables and this now works.

Thanks for the input

[v-yalanwu-msft]

Hi, @bigrods ;

Very happy to hear your problem solved; You can mark my reply in order to close the case to help people with similar questions to find answers. Thank you!

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @bigrods ;

Both one-to-one and one-to-many relationships are okay, because only the direction of filtering is considered, filtered by the employee table to the Assignment table;

Second, why the key column was created is to create a connection between the two tables, in order to achieve the connection with the staff employee table, in order to judge who can see and who can't see.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bigrods]

@v-yalanwu-msft 

The first dax statement doesn't work as there is a one-to-many relationshp between Staff and Assignment:

This is the relationship:

Secondly, what is the Dax for the Absence table? I assume it's

if(AbsenceType="Sickness","A","B")

Finally, what are you proposing the relationship between Staff and Absence is on? Is it the Key column? There is already a relationship that exists based on PersonID

Thanks

[v-yalanwu-msft · Accepted solution]

Hi, @bigrods ;

You could create a [Key] column in 'staff ' and 'Absence' table ; then create a relationship.

1.create a key column in 'staff ' table by dax.

Key = IF( RELATED(Assignment[Job Role]) in { "Board Level Director","Chief Executive"},"A","B")

2.create a key column in 'Absence ' table by dax.

3.create a relationship.

Then use USERPRINCIPALNAME() to implement Rls.

the final show:

If I am Jim:

If i am Lina 

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bigrods]

Hi @v-yalanwu-msft ,

Thanks for the reply; I'm not sure the solution works, as the relationship between Staff and Assigment (based on PersonID) is one-to-many - a Staff member could have multiple assignments. (apologies, my sample data didn't take this into account)

So I can't quite get the first Dax statment to create the Key column in the Staff table to work, would there be anything else that might work?

Many thanks for your time!