Forum Discussion
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
Hi Thomas_Eu
Please note the difference:
Within Power BI Desktop, username() returns a user in the format of DOMAIN\User and userprincipalname() returns a user in the format of [email protected].
Also, when publishing to Power BI Service, please assign roles to users as below
I make a simple sample with Power BI Desktop as below:
sample data
Manage role
View role
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello v-juanli-msft,
your example is my green example (show all projects of an manager). This was working before and is no problem.
My problem is the red example, whrere a manager schould see all projects of his department (we do have more than one Project manager within a department, but all should see all projects). In this case, I cannoct connect by the Manager, but I have to connect by the OrgBox.
If I do so, it works, if I use it as an filter, but it des not work in RLS.
For the moment I used a Workaround and added 30+ Roles and assigned each Manager tio a single role.
Hi Thomas_Eu
You could check this article, it should be useful
https://radacad.com/dynamic-row-level-security-with-manager-level-access-in-power-bi
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Thomas_Eu
Find a workaround
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.It looks like, it is a bit more difficult.
Your solution or the mentioned problem is working. But my Problem is bigger. I described it not enough.
I have multiple Access connection (e.g. OrgBox 1.1.0, should see all 1.1.0 projects and all 1.1.x projects). As soon as I will connect the second Join in the same way, I get the problem, that only one security filter is allowed.
I approached a internal IT specialist and will come back later.
So, here I am back, with my problem and a dataset to test around with :)
This time, I have reated a bigger example:
Excel-Version: https://drive.google.com/file/d/1451KZ5a3PzwLOpXKoGFdAGUUZJB4khPf/view?usp=sharing
PBIX-File: https://drive.google.com/file/d/1lBgtZv2odcAvEKCcdcMy2Wu9-ZAkF40w/view?usp=sharing
Every Project is assigned to an OrgBox (e.g. 1.1.3). Now I would give the following rights:
Each member of an OrgBox (e.g. 1.1.3), should see all Projects of his OrgBox (e.g. 1.1.3)
Each Manager of an OrgBox-Group (e.g. 1.1.0), should see all Projects the connected Boxes (e.g. 1.1.1, 1.1.2, 1.1.3, ...)
Each Top-Manager should see all Projects, in an Org-Box, starting with 1
I can create a Many:Many connection (Security enabeled) for one of the levels and it works fine.
But as soon, as I start to add all 3 connections, the RLS is no longer working (when tried with 3 Roles).
This is somewhat "stange" and feeles like an "bug" in Power BI, as a user normally can have multiple roles and each access rule is just "added"(if you can see 5 Projects, beacuse auf Rule 1 and 3 because of Rule 2, than you see 8 in total). But as soon, as you use "Security enabled", this is no longer working.
If anyone has an idea, this would be great. We could completly transform the original tables, no problem.
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