dynamic row level security

5 Topics

Dynamic RLS with multiple tables
Hi there. I have a reasonably complicated dynamic RLS which i am trying to resolve through dax. Attached is the schema. The premise is the Id in the user table is the USERRPINCIPALNAME The user has a a list of companies they are allowed access to in the USERALLOWED table These companies have a relevant ID called InstanceId-CRMId that gets passed into a site list and on to the report to limit the data. The issue i am haivng is i do not want to have to use bidirectional relationships There iaremany to many relationships Performance is poor I cannot seem to get the RLS to properly work and the sites table isnt filtering when I add ID = USERPRINCIALNAME() in the user table. Can someone suggest specifc dax for this use case please??? I have looked at some RADACAD posts on this example but can't work it out. Thanks in advance!!
Solved
debenaire
2 years ago Place DAX Commands and Tips
2.5KViews
0likes
5Comments
Help on RLS in Power BI
Hi Team - We have tabular model (screenshot below) and enabled RLS on the "DimSubsidiary" table, which is applying security filter to the "FactSales" tables. However, we have a requirement to show sales different when slicer is placed on Geography. Example: As per Sample Data below, User A is allowed to see US and UK Subsidiary Data For User A: Total Sales (Subsidiary=US) = 100 + 600 ( Row 1 + Row 2) . This is working as expected. Total Sales (For Geo=US) = 100 ( because of RLS user A can see Row 1 and Row 2) and For US, it's 100. but User A has access to US subsidary , they should be able to see Revenue for US Geography i.e they should see sales as ( 100 (Row1) + 500 (Row 3) + 700 (Row 5) Beacuse of RLS in place this will not work Can you please suggest how to proceed further in order to show 1300 as sales for User A when we select Geo="US" ? Do I need to create any bridge table based of Fact? Data Model Thanks, Abhiram
abhiram342
3 years ago Place DAX Commands and Tips
610Views
0likes
2Comments
Conditional/Multi-Layered Row-Level-Security
Hi Power BI Community, I am having trouble figuring out a multi-layered, dynamic RLS problem. Essentially, I have a table of transactions and employee email adresses. I also have another table of employee emails and their departments. Depending on which department the user belongs to, I want to provide them with either: Access to their own transactions only Access to 1 or more department's transactions How do I go about implementing this?
PMXMX
3 years ago Place DAX Commands and Tips
760Views
0likes
1Comment
Dynamic RLS using Dataverse tables works until the users try Accessing
Please advise. I set up Dynamic Row Level Security for a report that uses a table from DataVerse as my security table (with email addresses). The email address field is formatted as Email Address in the table. I followed all of the steps in this video as much as they show (https://www.youtube.com/watch?v=Z3oLWtWABfo). I can test in Power BI desktop using the users' email addresses. I also tested the security (test as role) on the Power BI Diamond Workspace (after publishing). Everything worked great until I shared the report with two users who have Viewer access to the workspace. They receive the following message just as if they had no access to the data: "The report can't be viewed because the underlying dataset uses row-level security (RLS)." Here is the code I set up in the Modeling, Manage Roles: [RLS_EmailAddress] = USERPRINCIPALNAME() I've done some searching on forums and saw someone say that USERPRINCIPALNAME() needs to be in double quotes. That breaks this from working in the desktop and web versions. Addl info- I am bringing the data in all tables as a dataset(Import) rather than accessing the data through a DirectQuery connection. Please advise!
Solved
DerekCook0814
3 years ago Place DAX Commands and Tips
2.5KViews
0likes
4Comments
Row Level Security - Multiple Filter Criteria
I am trying to use RLS to filter some data according to a table which stores users territory information. The territory information is a table ("UserTerritories") consisting of both a Country and a Territory. Both of these fields can be wildcarded with a * to indicate they have access to either all countries or all territories within a country. Example "UserTerritories" information table shown here. This would indicate that the first user (User 100) has access to all of Germany plus the West and North territories in Spain. User 200 has access to the East and Paris territories in France. User 300 has access to all data. Username Country Territory 100 Germany * 100 Spain West 100 Spain North 200 France East 200 France Paris 300 * * Example "Account" data to be filtered using RLS: Account Country Territory A001 Germany West A002 Germany Berlin A003 Spain West A004 Spain North A005 Spain South A006 France North A007 France East A008 France West A009 France Paris I have been able to use SELECTCOLUMNS in my RLS rules (as shown below) to be able to filter either country or territory but this treats each of these fields individually rather than as a combination. What this means is that if a user's territory rules has a * for one of the territory fields then it would include all territories for any of the countries they have access to and not just the one specific country. var UserCountrySet = SELECTCOLUMNS( FILTER( 'UserTerritories', [Username] = USERPRINCIPALNAME() ), "Country", [Country] ) var UserTerritorySet = SELECTCOLUMNS( FILTER( 'UserTerritories', [Username] = USERPRINCIPALNAME() ), "Territory", [Territory] ) return ("*" IN UserCountrySet || [Country] IN UserCountrySet) && ("*" IN UserTerritorySet || [Territory] IN UserTerritorySet) Can anyone provide any guidance how I can filter the list of Accounts by seeing if it matches ANY of the row entries (i.e. the Country & Territory combination) in my "UserTerritories" table please? Any help greatly appreciated. Thanks!
Solved
asm495
3 years ago Place DAX Commands and Tips
5.4KViews
0likes
2Comments