RLS using an inactive relationship

Question

Hi all,&nbsp;I am implementing Row Level Security for a dataset and have run accross an issue I can't seem to get resolved.&nbsp; My tables and relationships are as follows:&nbsp;Dimension TableUser Name | User EmailAAA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; aaa@company.comBBB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;bbb@company.comCCC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ccc@company.comDDD&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ddd@company.com&nbsp;* Dimension table uses RLS on 'User Email' with the following filter where [user email] = USERPRINCIPALNAME() so that when AAA logins it is only able to see his details in fact table.&nbsp;Sales TableAccount | Order | SalesRep1234&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4567&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AAA1234&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3456&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AAA2345&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2355&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;BBB&nbsp;Customers TableAccount | SalesRep1234&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AAA1235&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AAA2345&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;BBB&nbsp;Customer Contacts TableAccount | Contact | ContactEmail1234&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Jane Smith&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;JaneSmith@1234.com1235&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Joe Black&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;JoeBlack@1235.com2345&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;Greg Albright&nbsp; GregAlbright@2345.com&nbsp;&nbsp;RELATIONSHIPSActive Relationship (many to many, cross filter single (DimensionTable filters SalesTable)= DimensionTable[User Name] &nbsp;--&gt; * SalesTable[SalesRep]&nbsp;Inactive Relationship (many to many, cross filter single (DimensionTable filters CustomerTable)= DimensionTable[User Name] --&gt; &nbsp;CustomerTable[SalesRep]&nbsp;Active Relationship = CustomerTable[Account] --&gt; CustomerContactTable[Account]&nbsp;Desired Table (Unfiltered):Account | SalesRep | CustomerContact |Contact Email&nbsp;1234&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Jane Smith&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;JaneSmith@1234.com1235&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Joe Black&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;JoeBlack@1235.com2345&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;BBB&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Greg Albright&nbsp; GregAlbright@2345.com&nbsp;&nbsp;Desired Table (Filtered when AAA logs in):Account | SalesRep | CustomerContact |Contact Email&nbsp;1234&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Jane Smith&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;JaneSmith@1234.com1235&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AAA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Joe Black&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;JoeBlack@1235.com&nbsp;&nbsp;&nbsp;CHALLENGEI am working with DirectQuery and Row Level Security, both of which have their own limitations.&nbsp;Any table I create based on the SalesTable does get filtered by RLS successfully.&nbsp; However, though I can create the Unfiltered Desired Table, it does not get filtered when AAA logs in, most likely because it is an inactive relationship.&nbsp;Any help is greatly appreciated!

greg_deckler · Answer

Yes, it is because of the inactive relationship. I'm not sure that helps. 🙂

v-yuta-msft · Answer

jsteckel&nbsp;,
&nbsp;
Could you build a one-many relationship between Sales table and Customers Table? For example, add index columns or some other flags column in the two tables and build a relationship:
SalesTable[Flag] --&gt; * CustomerTable[Flag]
&nbsp;
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jsteckel · Answer

v-yuta-msft,&nbsp;Thank you for the idea.&nbsp; I do have 2 relationships between SalesTable and CustomersTable.&nbsp; They are:&nbsp;Active:&nbsp; SalesTable[AccountNumber] = CustomerTable[AccountNumber]Inactive: SalesTable[SalesPerson] = CustomerTable[SalesPerson]&nbsp;It did work for the CustomerContact table to make the SalesPerson link active, unfortunately, it breaks other financial matrices in the reports.&nbsp; They are more important, so I will have to leave those relationships as they are.&nbsp;Perhaps I will have to make a seperate dataset\report and make the correct links active to work with Row Level Security.

Forum Discussion

RLS using an inactive relationship

3 Replies

Related content

Use calendar's inactive relationship in matrix visual?

Filter Matrix using inactive relationship

using inactive relationship

using inactive relationship

Problem with inactive relationships