Dynamic RLS based on multiple columns and many to many relationship

[adrianPadurariu]

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
a.b@aaa.com	Belgium	Benefits
b.c@aaa.com	Belgium	Mobility
d.e@aaa.com	Germany	Mobility
e.f@aaa.com	Germany	Mobility
g.h@aaa.com	Finland	Benefits
h.i@aaa.com	Finland	Mobility
j.k@aaa.com	Czech Republic	All
l.m@aaa.com	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!

[adrianPadurariu · Accepted solution]

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)

[johnt75]

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.

[adrianPadurariu]

Thank you! but what about the situations where I have a user that needs to see the Country and all BL, or a user who needs to see a BL for every Countries?