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

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
Highlighted
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?

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 119 members 1,563 guests
Please welcome our newest community members: