cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBIUser-Sap
Frequent Visitor

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.

 

PBIUserSap_0-1650913643368.png

 

 

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]

)

 

PBIUserSap_0-1650937602296.png

 

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

@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):

 

DataInsights_0-1650985887429.png

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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. 

 

@PBIUser-Sap,

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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.

yingyinr_0-1651223214264.png

yingyinr_1-1651223257839.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Okay, That's shared.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors