RLS using an inactive relationship

[jsteckel]

Hi all,

I am implementing Row Level Security for a dataset and have run accross an issue I can't seem to get resolved.  My tables and relationships are as follows:

Dimension Table

User Name | User Email

AAA              aaa@company.com

BBB               bbb@company.com

CCC               ccc@company.com

DDD              ddd@company.com

* 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.

Sales Table

Account | Order | SalesRep

1234        4567      AAA

1234        3456      AAA

2345        2355      BBB

Customers Table

Account | SalesRep

1234         AAA

1235         AAA

2345         BBB

Customer Contacts Table

Account | Contact | ContactEmail

1234         Jane Smith       JaneSmith@1234.com

1235         Joe Black         JoeBlack@1235.com

2345         Greg Albright  GregAlbright@2345.com

RELATIONSHIPS

Active Relationship (many to many, cross filter single (DimensionTable filters SalesTable)

= DimensionTable[User Name]  --> * SalesTable[SalesRep]

Inactive Relationship (many to many, cross filter single (DimensionTable filters CustomerTable)

= DimensionTable[User Name] -->  CustomerTable[SalesRep]

Active Relationship = CustomerTable[Account] --> CustomerContactTable[Account]

Desired Table (Unfiltered):

Account | SalesRep | CustomerContact |Contact Email 

1234         AAA          Jane Smith       JaneSmith@1234.com

1235         AAA          Joe Black         JoeBlack@1235.com

2345         BBB           Greg Albright  GregAlbright@2345.com

Desired Table (Filtered when AAA logs in):

Account | SalesRep | CustomerContact |Contact Email 

1234         AAA          Jane Smith       JaneSmith@1234.com

1235         AAA          Joe Black         JoeBlack@1235.com

CHALLENGE

I am working with DirectQuery and Row Level Security, both of which have their own limitations.

Any table I create based on the SalesTable does get filtered by RLS successfully.  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.

Any help is greatly appreciated!

[v-yuta-msft]

@jsteckel ,

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] --> * CustomerTable[Flag]

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]

v-yuta-msft,

Thank you for the idea.  I do have 2 relationships between SalesTable and CustomersTable.  They are:

Active:  SalesTable[AccountNumber] = CustomerTable[AccountNumber]

Inactive: SalesTable[SalesPerson] = CustomerTable[SalesPerson]

It did work for the CustomerContact table to make the SalesPerson link active, unfortunately, it breaks other financial matrices in the reports.  They are more important, so I will have to leave those relationships as they are.

Perhaps I will have to make a seperate dataset\report and make the correct links active to work with Row Level Security.

[Greg_Deckler]

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