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.
Please consider this painfully simple model...
The STORE and CALENDAR tables are DIRECTQUERY mode. The FACT_IMPORT table is IMPORT MODE.
I cannot modify the relationships to force referential integrity. It is grayed out.
When I create slicers from my STORE and CALENDAR tables, I get (BLANK) as a value because I cannot assume RI in my composite model.
If I change the model to all IMPORT or all DIRECTQUERY, all's well. Import assumes RI by default, and DirectQuery actually lets you force RI.
Is this a bug or a feature? I'd like to be able to force RI in a composite model, because my data is pristine. But it cannot be done as far as I can see. I also don't see it as a known limitation in the documentation.
Solved! Go to Solution.
@Anonymous - I think this is more classified as a "side-effect" of composite models that you can't enforce RI and it kind of makes sense why you cannot. In import mode, all the data is in the model so it knows if there is RI. In Direct Query, again, the RI is enforced at the database level. But for composite models, part of the data is in the model in import mode and part of it is in Direct Query (the source) which is not known until enumerated and it could change every enumeration theoretically so it cannot enforce RI.
@Anonymous , refer if these can help
https://blog.crossjoin.co.uk/2019/07/10/power-bi-slicers-show-values-that-do-not-exist/
https://www.youtube.com/watch?v=23qemkDqMVY
@Anonymous - I think this is more classified as a "side-effect" of composite models that you can't enforce RI and it kind of makes sense why you cannot. In import mode, all the data is in the model so it knows if there is RI. In Direct Query, again, the RI is enforced at the database level. But for composite models, part of the data is in the model in import mode and part of it is in Direct Query (the source) which is not known until enumerated and it could change every enumeration theoretically so it cannot enforce RI.
@Greg_Deckler Well said, and makes sense. Thanks! Though, I still wish there was a "Just trust me" checkbox in the relationship properties between DirectQuery and Import mode tables. Maybe someday. 😁
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 |
---|---|
106 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |