RLS doesn't work with bidirection dax in powerbi

Question

We are working with Power BI to create self service report and want to limit access based on login user. But it is not all that straight forward. Here's our security model:

Have 4 tables

Table1

UserID(Email) AreaID

======= ========

Email1 Area1

Email2 Area2

Email1 Area3

Email3 Area3

Email4 Area4

Table2 - (Bridge Table)

Area

=====

Area1

Area2

Area3

Area4

Table3

AreaID CustName EntityID

======== ======= =======

Area1 Customer1 01

Area2 Customer2 02

Area1 Customer3 01

Area3 Customer3 03

Area4 Customer4 04

Table4

EntityNo EntityName

======= =========

01 EntityA

02 EntityB

03 EntityC

04 EntityD

Scenario -

Table1 and Table3 have many to many relationship on AreaID. We suppose to apply dynamic RLS using UserID column of Table1 which can be filtered through logged in user. To handle many-to-many condition we also have a bridge table with distinct Area so Table1 and Table3 are connected to it with one-to-many.

Applied cross filter on both directions with apply security filter on both sides between Table1 and Table2(BridgeTable). All fine till now, But here the issue when we try to filter Table4 as there is no filter propagation from Table3 to Table4. To make this work we tried to pass filters through writing some logic within DAX.

On the created role for RLS we added a filter for Table4 as below

[EntityNo] IN SELECTCOLUMNS(

FILTER(

'Table3',

 RELATED(Table1[UserID])=USERPRINCIPALNAME()

),

"EntityID"

,[EntityID]

)

But we got error as,

The column either doesn't exists or doesn't have relationship with any table available in current context. Issue is known that we are skipping Table2 from the DAX, Not sure how to achieve it. Can someone guide on this?