Forum Discussion
Dynamic RLS based on multiple columns and many to many relationship
Hello,
I am trying to create a dynamic RLS, but the problem is that my model is a many to many relationship between users table and mapping table and I am not able to create an active relationship with Security filter aplied.
This is the model:
Here is a sample of data from the model
1. Fact
| dataset_entity | dataset_path |
| EBE | silver/semarchy/merchants/acceptorReferential |
| EBE | silver/sun/finance/ledgerAccount |
| EBG | silver/tri2/merchant/invoiceBulletin |
| EBG | silver/tri2/merchant/merchantData |
| ECZ | silver/tri/operation/tVrfVoucherRedemptionForm |
| ECZ | silver/tri/sales/tCusCustomer |
| EDE | silver/sharepointFiles/marketing/salesforceProducts |
| EDE | silver/sharepointFiles/operation/brandLookup |
| EDE | silver/sharepointFiles/operation/cmoCardDetails |
| EFI | silver/delicard/marketing/cards |
| EFI | silver/delicard/marketing/customers |
2. Mapping
| dataset_entity | Country | BL |
| EBE | Belgium | Benefits |
| EBG | UNKNOWN | |
| ECZ | Czech Republic | Benefits |
| EDE | Germany | Mobility |
| EFI | Finland | Benefits |
And this is my Users table (exel file in the sharepoint, can be edited anytime by the owner) this will be linked to mapping table:
| User | Country | BL |
| [email protected] | Belgium | Benefits |
| [email protected] | Belgium | Mobility |
| [email protected] | Germany | Mobility |
| [email protected] | Germany | Mobility |
| [email protected] | Finland | Benefits |
| [email protected] | Finland | Mobility |
| [email protected] | Czech Republic | All |
| [email protected] | All | Benefits |
So I need to create a dynamic RLS because users can be added or changed in a specific country and to give access to Country and BL
Thank you!
SOLUTION!
VAR _UserEmail = USERPRINCIPALNAME()VAR _UserBL =CALCULATETABLE(VALUES(Users_files_from_sharepoint[BL]),FILTER(Users_files_from_sharepoint, Users_files_from_sharepoint[User email] = _UserEmail))VAR _UserCountry =CALCULATETABLE(VALUES(Users_files_from_sharepoint[Country]),FILTER(Users_files_from_sharepoint, Users_files_from_sharepoint[User email] = _UserEmail))RETURN(CONTAINS(_UserBL, Users_files_from_sharepoint[BL], "eQ") &&CONTAINS(_UserCountry, Users_files_from_sharepoint[Country], "eQ"))||(CONTAINS(_UserBL, Users_files_from_sharepoint[BL], "All") &&CostMonitoring_Mapping_files_from_sharepoint[Country] IN _UserCountry)||(CONTAINS(_UserCountry, Users_files_from_sharepoint[Country], "All") &&CostMonitoring_Mapping_files_from_sharepoint[BL] IN _UserBL)||(NOT CONTAINS(_UserBL, Users_files_from_sharepoint[BL], "All") &&NOT CONTAINS(_UserCountry, Users_files_from_sharepoint[Country], "All") &&CostMonitoring_Mapping_files_from_sharepoint[BL] IN _UserBL &&CostMonitoring_Mapping_files_from_sharepoint[Country] IN _UserCountry)
3 Replies
Create new columns on your mapping table and user table, e.g.
Combined Columns = COMBINEVALUES ( "|", Mapping[Country], Mapping[BL] )and then use the new columns to create a many-to-many relationship, single direction so that users filters mapping.
SOLUTION!
VAR _UserEmail = USERPRINCIPALNAME()VAR _UserBL =CALCULATETABLE(VALUES(Users_files_from_sharepoint[BL]),FILTER(Users_files_from_sharepoint, Users_files_from_sharepoint[User email] = _UserEmail))VAR _UserCountry =CALCULATETABLE(VALUES(Users_files_from_sharepoint[Country]),FILTER(Users_files_from_sharepoint, Users_files_from_sharepoint[User email] = _UserEmail))RETURN(CONTAINS(_UserBL, Users_files_from_sharepoint[BL], "eQ") &&CONTAINS(_UserCountry, Users_files_from_sharepoint[Country], "eQ"))||(CONTAINS(_UserBL, Users_files_from_sharepoint[BL], "All") &&CostMonitoring_Mapping_files_from_sharepoint[Country] IN _UserCountry)||(CONTAINS(_UserCountry, Users_files_from_sharepoint[Country], "All") &&CostMonitoring_Mapping_files_from_sharepoint[BL] IN _UserBL)||(NOT CONTAINS(_UserBL, Users_files_from_sharepoint[BL], "All") &&NOT CONTAINS(_UserCountry, Users_files_from_sharepoint[Country], "All") &&CostMonitoring_Mapping_files_from_sharepoint[BL] IN _UserBL &&CostMonitoring_Mapping_files_from_sharepoint[Country] IN _UserCountry)
