Problems encountering RLS

[Schmidtmayer]

I am having an issue regarding RLS. Following sketch of the situation. Our company is doing a survey every quarter, in order to find out, how our management is performing, in the eyes of everyone.
The rules are the following: Some persons should see full results, other should only see their own results, a perfect scenario for RLS. The following data model is used, all tables are either are either Import or DirectQuery from MSSQL-Server:

Same colors indicate these columns are related. Other relations seem not to be the problem, as deactivating them doesn't change a thing.
Some explanations are in order, as we are a german company:

DimUsers (DimensionTable, RLS is applied here)
Anmeldename - User to check in regard to RLS, for me, it is Tom.Schmidtmayer.365@tasag.de 
BV - Personal ID within the company, for me, it is 4770

DimStreuungRLS (connection table to ensure, some people see everything, some see only their results)
Betrachter: Personal ID of the person, viewing the report
Betrachteter: Personal ID of the persons, which results should be seen

Via procedure, this table is created, linking some persons to only themselves, other to all Personal IDs in the company, for my example, there is just the row 4770 4770 in the table, as I should just see my own results

DimRoles (Dimension Table of Employees)
bv - Personal ID of the person
other columns are just additional information given for filtering, like departement, etc.

Fact Ergebnisse (FactTable of the survey, containing alle the results)
Appraisee_BV: Personal ID of the person, which was given a rating
Note: Rating of the person (range 1 to 6)

Following Measures are relevant:
Bewertung (i.e. the rating) = AVERAGEX( Fact Ergebnisse, Fact Ergebnisse[Note])

In order to implement RLS, I implemented the following role:

RLS should operate on DimUsers, just giving the rows matching the USERPRINCIPALNAME(). I used LOWER() as the column in the companies database might contain spelling errors regarding capitals at the beginning.

After that, I enabled Viewing as, first entering my adress also the RLS-role:

Last step, I created 2 tables:

First one (left)
Anmeldename from Users,
BV from Dim Roles,
Bewertung 

Second one (right):
BV from DimRoles,
Bewertung

The results can be seen here:

This where my questions start:
The left table is showing the right columns, just my userprincipalname, my personal id and my rating. The total row is showing the rating of the whole company, indicating, the whole data can be accessed although RLS is applying.

The right table further strengthens this idea, as, when erasing the userprincipalname, all ratings can be seen.

Desired results:

Left Table:
Total should just show 1.82 as the table entry itself.

Right table: Just my own results, so bv 4770 and Bewertung 1.82, with a total of 1.82

What am I doing wrong?

[Schmidtmayer]

@tex628 : Just read your message again. Thought it was about something else. Your suggestion solved the issue. Thanks 😃

[tex628]

That happens to me more than i'd like to admit! 🙂 Good luck with your application!

Br, 
J 

[tex628 · Accepted solution]

Hi @Schmidtmayer , 

Have you applied security filtering in both directions for your doublessided relationship between DIMStreuungRLS & DIM Roles? 

[Schmidtmayer]

@tex628 : Yes, as seen in the screenshot above, this relation is bidrectional.