Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
jsteckel
Regular Visitor

RLS using an inactive relationship

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!

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

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

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
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.