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!!
Hierarchical Row Level Security with Recursive Lookup
Hello all, in my data model, I am using dynamic row level security in a recursive hierarchy. It checks if the user has access to the organization or any parent organization and, if yes, shows the data record. Unfortunately, I cannot go beyond five levels (formula length limit) but need seven. Is it possible to make loop, recursion or list reduction in the formula, so that it just checks the whole path, regardless of the number of levels? This is my DAX-Formula at the moment: OR(OR(OR(OR( PATHITEMREVERSE(RELATED(Organizations[Org Path]), 1, integer) in calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname()), PATHITEMREVERSE(RELATED(Organizations[Org Path]), 2, integer) in calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname())), PATHITEMREVERSE(RELATED(Organizations[Org Path]), 3, integer) in calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname())), PATHITEMREVERSE(RELATED(Organizations[Org Path]), 4, integer) in calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname())), PATHITEMREVERSE(RELATED(Organizations[Org Path]), 5, integer) in calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname())) I imagine something like this (mixture of JavaScript and DAX): [calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname())].reduce((found, nextId) -> OR(PATHCONTAINS("path", nextId), FALSE) Regards, FabianCrossfilter and row level security
Hello, I have the following model for a report: I've been asked to implement RLS and I'm having some trouble figuring out the correct method. In my original model table 1 does not exist and is not required. Table 1 is added for RLS amd contains a list of users email addresses, and the IDs which represent the rows I want the fact table, table 2, to filter to. One ID can be accessed my multiple users in table 1 which is why it is a many>many relationship. The RLS is setup to use Userprinciple name to filter table 1 and subsequently table 2. This element seems to work fine, as I can see when RLS is applied, table 2 does filter to the correct IDs the person in table 1 has access to. Due to table 2 being the fact table and on the many of the many>one side, it does not pass these filters through to the associated dimension tables. I have been reading into this as it is new to me, and thought I could use crossfilter within my measures to make the filters flow through to the dimension tables. As an example, for a measure that counts the rows in table 3 I thought I could try something like the below to count the total rows in table 3, but only for the rows which match those filtered in table 2: CountRowsTable3 = CALCULATE( COUNTROWS(Table3), CROSSFILTER('Table2'[ColumnA],Table3[ColumnA],Both) When I do this calc I still get the total number of rows in table 3 without the RLS applied. Grateful for any steers on the best way to do this, or if I am down totally the wrong track happy to be told so too! Thank you
Rls
Please can anyone suggest how to implement dynamic RLS in Powerbi service so that external user can see only their data. I am trying to set up role wherein it checks the login email is same as USERPRINCIPALNAME. The user table stores the email id as [email protected]. The MS Active directory has the user set up as abcd_fakeorg.com#ext#@trytowork.onmicrosoft.com What DAX can I apply and where and howDisplay only certain columns for each role
Hi, I have an Excel file with several columns in which there are data, budget and Invoice amount of every warehouse of the company. I've created a row level security role for each warehouse on my powerBI file and imported the Excel. I would like to display only relative data for each role while hiding other columns. I tried to fix it on Tabular editor but I didn't find anything related to columns, only tables. Here's the link at the Excel file: https://drive.google.com/file/d/1wF81egZtQwejqL57Vig0ehYgcxpFTU8X/view?usp=sharingRow Level Security || Developer vs User view || Wrong Calculation || Measures not working
Hi all, I am a developer of a report and i have given access to many users by using Row Level Security. As a developer when i apply certain set of filters, i am getting the correct data i.e. measures are working properly. Cumulative exit and other measures are showing correct data, but when i apply the same filters for a test role(RLS) the measures are throwing different numbers. Developer view Test as a role ( Wrong Numbers, while same set of filters are applied)
