Forum Discussion
RLS doesn't work with bidirection dax
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?
9 Replies
First, change the bidirectional crossfilter to unidirectional (this is a best practice in data modeling). Here's the data model (I renamed the tables to be more descriptive):
Use the DAX below in the role for the Entity table:
[EntityNo] IN CALCULATETABLE ( VALUES ( AreaCustEntity[EntityID] ), CROSSFILTER ( AreaUser[AreaID], AreaBridge[AreaID], BOTH ), AreaUser[UserID] = USERPRINCIPALNAME () )The CALCULATETABLE function allows you to set the crossfilter direction to "both", which is necessary due to the unidirectional relationship between AreaUser and AreaBridge.
Thanks for checking!! DAX above works. But we face another issue, The record count (for AreaCustEntity) is different when RLS was applied through AreaUser to that of now where RLS is applied on Entity. All other tables in the model are filtered via AreaCustEntity so now we are not sure how we can fix this.
Can I reach you over mail for queries, anyway i am available at [email protected]
Glad to hear the DAX works. Would you be able to provide an example illustrating the record count issue?
