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.
Hello!
Hopefully someone has cracked the code here - I am trying to use dynamic row level security in a DirectQuery model and running into a road block.
The rest of my model is one to many relationships with referential integrity, sending over a nicely folded query. However, my dynamic RLS table has the potential for multiple rows and needs to be a many to many relationship. The assume referential integrity option is grayed out despite my understanding of many to many requiring referential integrity. Not being able to click it is resulting in a nasty query being folded and sent over instead of joining the dynamic RLS object inside of the query.
Any ideas or is this just a hard stop with PowerBI restrictions on assume referential integrity & many to many relationships?
Thank you!
@awhiteway - So there is no way to build a bridge table between your two tables with the many-to-many relationship? Best to avoid many-to-many relationships and use bridge tables if at all possible in my opinion.
I had the same thought - but while I could put a bridge table and enable it there- the upstream table not being part of the folded query still causes the performace hit. Essentially it seems a many to many relationship can't exist anywhere in the chain? That makes Dynamic RLS very tough to do
Hi @awhiteway ,
"Essentially it seems a many to many relationship can't exist anywhere in the chain?"
-In the direct query mode, the many-to-many relationship is not impossible to exist, but needs to be avoided, such as using bridge tables, because it can lead to query statements that don't perform well.
In addition, your report performance depends largely on the performance of the underlying data source in direct query mode, so although creating a bridge table may reduce performance, it is a more feasible method
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |