User Profile

Thomas_Eu

Helper I

Joined 7 years ago

19 Posts3 Solutions

View All Badges

User Widgets

Contributions

Re: 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
6 years ago Place Desktop
3.1KViews
0likes
0Comments
Re: Problem with dynamic RLS (Many : Many)
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.
6 years ago Place Desktop
2.8KViews
0likes
1Comment
Re: Problem with dynamic RLS (Many : Many)
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.
6 years ago Place Desktop
2.8KViews
0likes
0Comments
Re: Problem with dynamic RLS (Many : Many)
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.
6 years ago Place Desktop
3.1KViews
0likes
5Comments
Re: Problem with dynamic RLS (Many : Many)
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)
6 years ago Place Desktop
3.1KViews
0likes
1Comment
Re: Problem with dynamic RLS (Many : Many)
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?
6 years ago Place Desktop
3.1KViews
0likes
3Comments
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: 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
Solved
6 years ago Place Desktop
Need Help
3.2KViews
0likes
12Comments
Re: How to create an filtered and embedded link
Hmmmmm, look like it is not possible right now with an "easy" solution (I am not able to write javascript). Tried a workaround, with publishing the report in an app and to hide it in the navigation, but did not work either (a report in an app can be filtered, but will not open, if the navigation is hidden). I opend an poll, to integrate this function: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/38379445-allow-filters-in-the-url-for-an-embedded-report
7 years ago Place Developer
998Views
0likes
1Comment
Re: How to create an filtered and embedded link
Has no one ever created a filtered and embedded link? Could not imagine, that I am the first one looking for such a possibility...
7 years ago Place Developer
1KViews
0likes
0Comments
How to create an filtered and embedded link
Dear all, I want to create an filtered and embedded link. I can create a filtered link (see Filtered) from my report (see PureLink), but this link opens the report in the Power BI framework. But I want to embedd the report. I can also embedded the complete report (see Embedded), but than I lose the filter. I have already tried to follow the instroctions here (see Own-Test), but then I get the message, that I do not have the rights to view the report (but I have). Can anyone help with the correct syntax? PureLink: xxxxs://app.powerbi.com/groups/0fbd9b78-1b46-431c-80c6-459da90c0008/reports/397a9665-1e6e-42f8-8312-2be45d9e04f1/ReportSection65b0493b646d23eb40e6 Filtered: xxxxs://app.powerbi.com/groups/0fbd9b78-1b46-431c-80c6-459da90c0008/reports/397a9665-1e6e-42f8-8312-2be45d9e04f1/ReportSection?filter=View/Project eq 'Project1' Embedded: xxxxs://app.powerbi.com/reportEmbed?reportId=397a9665-1e6e-42f8-8312-2be45d9e04f1&autoAuth=true&ctid=0e17f90f-88a3-4f93-a5d7-cc847cff307e&config=eyJjbHVzdGVyVXJsIjoiaHR0cHM6Ly93YWJpLW5vcnRoLWV1cm9wZS1yZWRpcmVjdC5hbmFseXNpcy53aW5kb3dzLm5ldCJ9 Own-Test: xxxxs://app.powerbi.com/reportEmbed?reportId=397a9665-1e6e-42f8-8312-2be45d9e04f1?filter=View/Project eq 'Project1'&autoAuth=true&ctid=0e17f90f-88a3-4f93-a5d7-cc847cff307e&config=eyJjbHVzdGVyVXJsIjoiaHR0cHM6Ly93YWJpLW5vcnRoLWV1cm9wZS1yZWRpcmVjdC5hbmFseXNpcy53aW5kb3dzLm5ldCJ9
7 years ago Place Developer
Need Help
1KViews
0likes
4Comments

Data Privacy

Microsoft Fabric Community and Privacy

To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.