Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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!
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
87 | |
81 | |
64 | |
49 |
User | Count |
---|---|
123 | |
109 | |
88 | |
68 | |
67 |