Problem with dynamic RLS (Many : Many)

Hello together,

now coming back with the solution :)

Fist:

The way you all mentioned works. You can use a many:many connection with the RLS, IF you apply the Security-Filter AND IF you just use one of these connections. In case of an access through a many:many connection, just one "Both" filter (with Security) is allowed.

My problem:

As I had to access through 3 times a many:many connection (each Manager-Level), this Solution is not working.

Solution:

I had to build a new file, where each connection is in the same column (a level indicator has to be attached, to differentiate). Than I had to apply an left outer Join to add the emails. By this solution, I get a final table, where each project is connected to the managers multiple times. I can now use this new Table (filtered in both directions and using the security filter) as access-connection.

Steps to do in Power Query:

let Quelle = #"Projects",
Unpivot = Table.UnpivotOtherColumns(Quelle, {"Project"}, "Attribut", "Wert"),
#"Merge Level & Box" = Table.AddColumn(Unpivot, "Combination", each [Attribut] &" / "& [Wert]),
#"Remove Other" = Table.SelectColumns(#"Merge Level & Box",{"Project", "Combination"})
in #"Remove Other"

let Quelle = #"User",
#"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(Quelle, "Combination", each if Text.End([OrgBox],1) <> "0" then "L3-Level / " & [OrgBox] else if Text.End([OrgBox],3) <> "0.0" then "L2-Level / " & [OrgBox] else "L1-Level / " & [OrgBox]),
#"Andere entfernte Spalten" = Table.SelectColumns(#"Hinzugefügte benutzerdefinierte Spalte",{"User", "Combination"})
in #"Andere entfernte Spalten"

let Quelle = Table.NestedJoin(#"Access-Conection Projects", {"Combination"}, #"Access-Connection User", {"Combination"}, "Access-Connection User", JoinKind.LeftOuter),
#"Connect by LeftOuterJoin" = Table.ExpandTableColumn(Quelle, "Access-Connection User", {"User"}, {"EMail"})
in #"Connect by LeftOuterJoin"

I attached the final pbix-File, as it is easier to understand within the file: https://drive.google.com/file/d/1iCS-fbpR4614Xk8IvEPO6zZqStEr17Uh/view?usp=sharing