Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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.
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.
Glad to hear the DAX works. Would you be able to provide an example illustrating the record count issue?
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
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.
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
Okay, That's shared.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |