Forum Discussion

aaverbec's avatar
aaverbec
Regular Visitor
9 years ago

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
[email protected]CABU025
[email protected]USBU026

 

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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft 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

    • aaverbec's avatar
      aaverbec
      Regular Visitor
      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.
      • Anonymous's avatar
        Anonymous
        Not applicable

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