Row level security from two tables.

[mohit_K]

I have a data table as shown below.

Territory ID	Value	SKU ID
1	20	22
1	30	23
1	40	22
2	50	23
2	60	22
2	70	23

I have two other tables one for Territory ID mapping and the other for SKU ID mapping.

Territory ID	Email Id
1	S@gmail.com
2	A@gmail.com

SKU	Email
22	Mk@gmail.com
23	A@gmail.com

I Want to apply Row Level Security such that the rows in the data table are filtered according to the logged-in user and they are able to view only the SKU and Territory assigned to them.

Desired Output:
For	S@gmail.com
Territory ID	Value	SKU ID
1	20	22
1	30	23
1	40	22
For	Mk@gmail.com
1	20	22
1	40	22
2	60	22
For	A@gmail.com
2	70	23

how should I go about creating Row-level Security on these two independent tables?

[mohit_K]

@v-jingzhang  Can you please help with this? It's very urgent for us

[v-jingzhang]

Hi @mohit_K 

Sorry for the late reply. Please download the attachment for details.

Filters:

VAR skuEmails = VALUES ( 'SKUTable'[Email] )
VAR loginUser = USERPRINCIPALNAME ()
RETURN
    IF ( loginUser IN skuEmails, 'SKUTable'[Email] = loginUser, TRUE () )

VAR territoryEmails = VALUES ( 'TerritoryTable'[Email Id] )
VAR loginUser = USERPRINCIPALNAME ()
RETURN
    IF (
        loginUser IN territoryEmails,
        'TerritoryTable'[Email Id] = loginUser,
        TRUE ()
    )

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[v-jingzhang]

Hi @mohit_K 

I create a sample based on your description. You could download the attached pbix file for details.

In short, create relationships between TerritoryTable/SKUTable and DataTable based on Territory ID/SKU ID. Then add a filter on the DataTable which refers to the other two tables.

VAR skuEmails = VALUES ( 'SKUTable'[Email] )
VAR territoryEmails = VALUES ( 'TerritoryTable'[Email Id] )
VAR loginUser = USERPRINCIPALNAME ()
RETURN
    IF (
        ( loginUser IN skuEmails )
            && ( loginUser IN territoryEmails ),
        RELATED ( 'TerritoryTable'[Email Id] ) = USERPRINCIPALNAME ()
            && RELATED ( 'SKUTable'[Email] ) = USERPRINCIPALNAME (),
        RELATED ( 'TerritoryTable'[Email Id] ) = USERPRINCIPALNAME ()
            || RELATED ( 'SKUTable'[Email] ) = USERPRINCIPALNAME ()
    )

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[mohit_K]

Hello @v-jingzhang,

The relationship mentioned between the two tables in the question follows a one to many relationship
on the other hand we will be using a many to many relationship for the mapping between the two tables.
I tried creating the filter and the relation sent by you in the previous answer for the many to many relation :-

But I am getting the following error when I try to create the filter on the DataTable :-

The error message reads :- The column 'TerritoryTable[Email Id]' either doesn't exist or doesn't have a relationship to any table available in the current context. 

Can you please help me with this error?