How to set up RLS for One to Many relationship

[PriyankaSar]

I have 2 tables as below,

1. MasterList (This table has IDs which are connected to IDs in Team list)
2. TeamList (This table has IDs multiple times and email IDs who are related to ID)

There is one to many relationship with both option on.

I tried below code,

[Title] IN

CALCULATETABLE(VALUES('TeamCoach'[ID]),
FILTER ('TeamCoach',
'TeamCoach'[ID] =
LOOKUPVALUE (TeamCoach[ID], TeamCoach[TCemail],USERPRINCIPLENAME())
)
)

This is working when we get only one record.

When we get multiple records then it gives an error. Also when I try to use VAR TC it gives syntax error.

Can anyone help me get this right?

[Anonymous]

Hi @PriyankaSar ,

Thank you @lbendlin  for your prompt reply. Based on the details you provided, it appears that the formula you shared is intended to filter the TeamCoach table based on the USERPRINCIPLENAME() function, which is a common method for dynamic RLS setups. Here are a few ways you may be able to resolve your issue:

1.As @lbendlin  said, the LOOKUPVALUE function is intended to return a value. This may result in errors or unexpected behaviour when multiple records match the condition, which seems to be part of the problem you are facing. In addition, the use of VARs requires a specific syntax, which may lead to the syntax errors you mentioned.

2.For cases involving multiple records and ensuring that RLS is applied correctly, it is often more efficient to filter the table directly based on the relationship and the user's email without relying on LOOKUPVALUE.

Title =
CALCULATE (
    VALUES ( 'TeamCoach'[ID] ),
    FILTER (
        'TeamCoach',
        'TeamCoach'[TCemail] = USERPRINCIPLENAME()
    )
)​

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

with both option on

You don't want to do that for RLS, unless you have rules on both sides.

It should not be necessary to use LOOKUPVALUE.  The data model can do the RLS work for you if you apply the proper directions.

[PriyankaSar]

When i try to select Apply Filter on both the sides, it gives me error. I have set cross filter type "Both" but still not working.