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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MiraNi
Advocate III
Advocate III

RLS for dimension tables based on fact table RLS

In SSAS tabular model, I have a RLS defined on fact tables by using security table through lookup function. When user logs in, it checks user's userprinciplename() and if it has any access allowed to fact table rows based on vendor number. Power BI- reports are using live connection to tabular model.

Now customer wants to limit also the data in dimension tables joined to fact tables so, that user could only see dimension data, which has been used in fact tables. For example user can see only those products, which have been used in the transactions of user's vendor no. There are so many fact tables, brigde tables and dimensions, that it is not possible to fix this by changing the relatioship properties.

I got an advice to use IN -function but it doesn't work. Let's use one dimension and fact table as an example. I have a fact table FactPurchase and dimension table DimPurchaseAgreement with relationship using PurchaseAgreementKey column.  I already have a RLS restricting the row visibility for FactPurchase based on VendorNo based on user and that is working fine. Now I want to restrict visibility to show only rows in DimPurchaseAgreement -table, when PurchaseAgreementKey can be found in FactPurchase -table AFTER the VendorNo RLS has first restricted it's rows for user. I have used this DAX for DimPurchaseAgreement  = [PurchaseAgreementKey] IN CALCULATETABLE(VALUES('Purchase'[PurchaseAgreementKey])). It doesn't work - the dimension table still shows all rows in report. Can anybody give me any help for this solution or offer a new one?

1 REPLY 1
MiraNi
Advocate III
Advocate III

I just learned, that CALCULATETABLE function doesn't work with live connection, only with import mode. So I would need a new idea for this.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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