Forum Discussion
Circular Dependency in creating relationship and implement RLS
Hello Everyone,
Whenever the user logs in, he/she should see their own data based on their permission on Organization, Branch and Area entity.
Users may not have permission to the entire Area, only to a specific Branch.
To implement Row Level Security(RLS) based on Organization, Branch, and Area, I have set the relationship as in the file attached.
But, I am unable to set the active relationship between Branch and Contact, Area and Contact as well as Contact and Message. I have to give the slicer of Company, Branch, Area, Contact, User, Role and Client.
Please guide as to how I should apply RLS as well as each entity filters other entities.
You can find the sample file here.
Please know that I have gone through these articles but wasn't able to come up with optimal solution.
- https://www.sqlbi.com/articles/understanding-circular-dependencies/
- https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/
Thanks in advance.
Hi rajulshah,
After checking your sample file, I found you can't multiple bridge tables and try to link and active these relationships at the same time. As I said, current power bi only supports one major relationship activated.
In my opinion, I'd like to suggest you use concatenate relationship fields as relationship keys instead build relationships in multiple fields.
You can refer to the following blog to extract and merge your fields to a table, then concatenate each field as a unique key. (notice: please also add this concatenate field on your fact table side)
Relationship in Power BI with Multiple Columns
After these steps, you can create a relationship based on concatenating unique key to apply filter effect on multiple column conditions at the same time.
Regards,
Xiaoxin Sheng
3 Replies
Hi rajulshah.
I think you need to modify your table relationship mapping to keep one major relationship with both direction mode and turn on 'apply security filter on both direction' option to keep RLS filter across the relationship.
Relationships in analysis services tabular models
Regards,
Xiaoxin ShengHello Anonymous,
Thanks for the response.
We tried changing the direction mode to 'both direction' but still wasn't able to resolve the error of circular dependency.
Please see the file with updated relationships here.We are not able to assess how to modify relationships so that RLS is applied with both direction mode turned on.
Can you please help me further with this?
Thanks a lot for your help.
Hi rajulshah,
After checking your sample file, I found you can't multiple bridge tables and try to link and active these relationships at the same time. As I said, current power bi only supports one major relationship activated.
In my opinion, I'd like to suggest you use concatenate relationship fields as relationship keys instead build relationships in multiple fields.
You can refer to the following blog to extract and merge your fields to a table, then concatenate each field as a unique key. (notice: please also add this concatenate field on your fact table side)
Relationship in Power BI with Multiple Columns
After these steps, you can create a relationship based on concatenating unique key to apply filter effect on multiple column conditions at the same time.
Regards,
Xiaoxin Sheng
