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
aaverbec
Regular Visitor

Dynamic Row Level Security on intersection of 2 fields from different tables

I have a requirement for dynamic security that I haven't been able to figure out.  Not sure if it's a tool limitation or just requires more complicated DAX than I understand.

 

The requirement is to secure on a combination of Country and Business Unit.   These are both attributes/master data fields.  So I have a fact table, and then 2 dimension tables.  (1 dimension table contains Country, 1 contains BU)

 

Below is an example from the Security table

 

UserCountryBusiness Unit
Joe_Smith@abc.comCABU025
Joe_Smith@abc.comUSBU026

 

When I try to add the DAX filter logic, I have to pick 1 of the dimension tables to apply the filter, and if I try to reference the column from the 2nd dimension table, I get an error during the syntax check.

 

"The column 'table2[Country]' either doesn't exist or doesn't have a relationship to any table available in the current context."

 

I was able to get this to work in a POC I did but both Country and Business Unit were on the same table in that example.

 

 

3 REPLIES 3
v-huizhn-msft
Employee
Employee

Hi @aaverbec,

If there is a relationship betweem the fact table and dimension table. You should create a relationship between the tables, then use the RELATED function to get the value in fact table. Could you please share your sample data for further analysis?

Best Regards,
Angelia

The two fields we are securing on come from 2 different dimension tables that both relate to the fact table.  Let me know if the pictures below are not sufficient.
Model.JPGBus_Unit Filter.JPGPlant Filter.JPG

I have the same use case. Have you found any solution or workaround?

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.