cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aaverbec Frequent Visitor
Frequent 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 Super Contributor
Super Contributor

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

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

Highlighted
aaverbec Frequent Visitor
Frequent Visitor

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

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
savvari Frequent Visitor
Frequent Visitor

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

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