Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Thomas_Eu
Helper I
Helper I

Problem with dynamic RLS (Many : Many)

Dear all,

 

I have a problem, applying a RLS with the Username.

 

I have a project List, from which I would only show a few projects, based on the entries in a user-file.

 

The data structure looks as follow:

RLS.png

 

As long as I create a Many : One connection (just one line per Manage in the dimension table), the solution is working fine.

 

But If I want to show an manager all projects of his OrgUnit, than I get into trouble. Than I get an Many : Many connection.

 

This type of connection IS working, if I apply an Slicer in the Report and manually select a email, to filter for (than only the projects for this Email are shown). 

 

But as soon, as I use it as an Dynamic-RLS-Connetor (Role -> Filter -> Email = username()), the manager can see all projects in the online-report.

 

I have no idea, what I should change and why it is working in Power Bi Desktop (in a slicer), but not in the Power Bi Service (when used for RLS).

 

Any ideas?

 

 

Best regards,

Thomas

1 ACCEPTED SOLUTION

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

 

View solution in original post

12 REPLIES 12
v-juanli-msft
Community Support
Community Support

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 user@contoso.com.

 

Also, when publishing to Power BI Service, please assign roles to users as below

https://docs.microsoft.com/en-us/power-bi/service-admin-rls#validating-the-role-within-the-power-bi-...

 

I make a simple sample with Power BI Desktop as below:

sample data

Capture9.JPGCapture10.JPG

Manage role

Capture11.JPG

View role

Capture12.JPG

Capture13.JPG

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 

 

 

 

 

Hi @Thomas_Eu 

Find a workaround

Capture10.JPGCapture11.JPG

Capture12.JPG

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.

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.

 

 

Multiple.png

 

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

 

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.

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

Capture21.JPG

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.

RobbeVL
Impactful Individual
Impactful Individual

Hi there,

 

Seems like you will need another Unit dimension. (unique Unit values)

Create another dimension between the many to many tables (you will need to set connection to both, in order to filter corrrectly)

 

Hope this helps

Robbe

Just to clarify: 

 

A) Dimension-Table <-Both-> Only unique Unit ->Single-> Data-Table

B) Dimension-Table ->Single-> Only unique Unit <-Both-> Data-Table

C) Dimension-Table <-Both-> Only unique Unit <-Both-> Data-Table

 

Old New Question

 

Which is the correct solution?

RobbeVL
Impactful Individual
Impactful Individual

Before you're edit I was going to say, A for sure.
After I would still stick with A 🙂  and tick the box "apply security filter in both directions" (under rel. type)

 

Let me know if this works.

 

Robbe

Hello RobbeVL,

 

tried the solution, but now, the RLS does nothing. If the user opens the report, he does not see any project.

 

Really interesting is, that I can use the ResourceEmailAdress in an slicer (to test it) and it filters the view. But as soon, as it is used with the RLS, it does nothing...

 

This is how it is programmed now:

Access to Connection = Many : Many (Both) / Also tried Many: One (Both)

Connection to Datatable = One : Many (Single)

 

RLS2.png

 

RobbeVL
Impactful Individual
Impactful Individual

Should be a 1 to many relationship
And why is 1 hidden?

 

Robbe

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.