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
Table2 - (Bridge Table)
AreaID CustName EntityID
======== ======= =======
Area1 Customer1 01
Area2 Customer2 02
Area1 Customer3 01
Area3 Customer3 03
Area4 Customer4 04
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(
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?
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@example.com
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.
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.
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.