Bi-directional and many-many relationship in Dynamic Row level security

[sivarajan21]

Hi,

I have below dynamic RLS(row level security) for my data:

This relationship perfectly filters my data but unfortunately below bi-directional relationship between user allowed table and company management table causes performance issues.

Also, I have read many articles that many-many relationships are not ideal for power bi modeling and the below many-many between user allowed table and core user table also causes concern:

so want to avoid many-many and bi-directional relationship.

Can someone please guide, is there any better approach like(Dax) to resolve this issue?

Please let me know if you need further  info.

Thanks in advance!

@Ahmedx @Greg_Deckler @amitchandak @Ashish_Mathur 

[sivarajan21]

Hi,

Please can someone help me with the below RLS dax measure as it is giving me below error:

Dax measure is:

[Company Id] IN SELECTCOLUMNS(
FILTER(
'UnifyLive_UserAllowedCompany',
RELATEDTABLE(UnifyLive_Core_User[Id])=USERPRINCIPALNAME()
),
"Company Id"
,[Company Id]
)

Below is the model:

Thanks in advance!

@Ahmedx @marcorusso @amitchandak @Greg_Deckler 

[amitchandak]

@sivarajan21 , I am assuming the "core user" is RLS table. it is fine to have many to many with RLS table, because after the filter it will act like 1-many

But the other "1 to many" between your fact and dimension should be single directional

[sivarajan21]

Hi @amitchandak ,

Thanks for your quick response!

Could you please help me how to achieve this 'But the other "1 to many" between your fact and dimension should be single directional'?

Thanks in advance

@Ahmedx @amitchandak