RLS doesn't work with bidirection dax

[PBIUser-Sap]

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?

[DataInsights]

@PBIUser-Sap,

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.

[PBIUser-Sap]

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 jbisoi42@outlook.com

[PBIUser-Sap]

Hi,

Sure.. 

for example, When RLS is applied theough AreaUser total count of AreaID in report layer is different than when RLS is applied through Entity table using above DAX. 

[DataInsights]

@PBIUser-Sap,

Glad to hear the DAX works. Would you be able to provide an example illustrating the record count issue?

[PBIUser-Sap]

If it is more convenient to you I can share recordings of the example with you. If you agree with that please share a contact if possible

[v-yiruan-msft]

Hi @PBIUser-Sap ,

In order to provide you a suitable solution quickly, could you please share some simplified sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and specific examples. It is better if you can share a simplified pbix file.  You can also share these info with us by private message. Thank you.

How to upload PBI in Community

Best Regards

[PBIUser-Sap]

Sure, How can share with you via private message. Can you please share me a mail id contact...

[v-yiruan-msft]

Hi @PBIUser-Sap ,

You can click my account name and access to profile page. Then click "Send this user a private message" to send the private message to me.

Besides that, you can remove the sensitive info in your simiplified pbix file before sharing, then refer the following link to share the file with me. Thank you.

How to upload PBI in Community

Best Regards

[PBIUser-Sap]

Okay, That's shared.