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 ...
DataInsights
Super User
Super User
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.