Forum Discussion

Huguo90's avatar
Huguo90
Icon for Helper I rankHelper I
3 years ago

RLS with 4 tables

I want to do an RLS, but I have 4 tables I'm looking for a lookupvalue, to display a manager or a user. But I can't do it. I have trouble getting out of it. Here is what I did, but it doesn't work.


[ID]=
LOOKUPVALUE(
'Contact'[AccountId],
'The_users'[Email], UserPrincipalName(),
'Contact'[AccountId],
'Account'[ID])

Or I want to improve this but I can't

IF(
MAX(
FILTER('The_users_2','The_users'[Email]=USERPRINCIPALNAME()),
'The_users'[Distributor])=0,
'The_users'[Email]=USERPRINCIPALNAME(),
1=1)

how to add another condition in this formula, like: if Account'[ID]= The_users[AccountId], but it doesn't work.

The goal is to display both when what Users can see in addition to displaying their games that they can see in the RLS

Need Help

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi Huguo90 

     

    Do you create any relationships between 4 tables? I would suggest that you create relationships between tables to pass the filtering rather than using LOOKUPVALUE. This would make it easier and faster. 

     

    For example, create a relationship between 'Account' and 'The_users' tables on 'Account'[ID] and The_users[AccountId]. Note that if your DAX expression is performed on 'The_users' table, ensure that the relationship is Single (from 'The_users' to 'Account') or Both. The arrow direction on the relationship line represents the cross-filter direction between two tables. 

     

    Similarly, create relationships between 'The_users' and 'Contact' tables or between 'Account' and 'Contact' tables. This is per your need. The purpose is to make one table filter another table through the relationship. 

     

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