To optimize your Power BI data model when using DirectQuery, it is important to follow these best practices.
Understanding DirectQuery behavior
When using DirectQuery, Power BI translates DAX queries on the fly into SQL code, which is then executed against the underlying database.
Because of this, DirectQuery comes with certain limitations when using complex DAX functions. The complexity of your data model also impacts how efficiently Power BI can generate SQL statements — and therefore directly affects query performance.
Recommended table modes
Typically, it is the Fact table that should be set to DirectQuery mode.
It is recommended that related Dimension tables are set to Dual mode to balance performance and flexibility.
⚠️ If you combine a Dimension table in Import mode with a Fact table in DirectQuery mode, Power BI will create Limited relationships.
Strong vs. Limited/Weak relationships
Limited relationships are visualized by a half circle on the relationship line in the Model view of your Power BI semantic model.
If all your relationships are strong, you will not see any half circles.
It goes without saying that strong relationships are much better for both performance and data accuracy in Power BI.
Why strong relationships matter
1. Performance and Query Folding
In DirectQuery mode, Power BI translates visuals and DAX calculations into SQL queries.
When relationships are strong and well-defined, Power BI can generate optimized SQL joins that fold efficiently to the source database.
Limited relationships often prevent query folding, causing Power BI to retrieve unnecessary data or perform joins locally — which significantly reduces performance.
A Limited relationship can force Power BI to pull entire tables into memory and join them client-side — which is extremely slow.
Changing table storage mode (Import, DirectQuery, Dual)
As of November 2025, if you have already imported tables in Import mode, you cannot change them directly to DirectQuery or Dual mode from within the standard Power BI interface.
However, you can update the mode manually in the TMDL (Tabular Model Definition Language) area of Power BI Desktop:
Open the TMDL view in your Power BI project.
Drag all tables onto the canvas.
Make a copy for the script in a separate tap for backup.
Search for the property “Mode:” for the table you wish to change.
Replace the existing value (Import) with either "DirectQuery" or "Dual".
Press Apply.
💡 Tip: Before applying the changes, you can copy the TMDL script to a separate tab as a backup. This allows you to revert easily if needed.
If this integrity can be assumed (i.e., the data really is clean), Power BI can safely generate INNER JOINs instead of LEFT OUTER JOINs in the SQL it sends to the source.
This typically improves query performance, since INNER JOINs are faster to execute and return fewer rows.
If the option is not enabled, Power BI must assume referential integrity might be broken, so it uses LEFT OUTER JOINs to preserve all fact table rows.
This can lead to a significant performance penalty.
Therefore, it is recommended to set “Assume referential integrity” = YES on all relationships connected to DirectQuery tables only if you are sure the data in both tables is consistent (no missing or mismatched keys).
Related resources
For more information about combining Import and DirectQuery tables for writeback and performance optimization, please visit: https://kb.accobat.com/kb/guide/en/import-and-direct-query-KRh0hWhiNs/Steps/3137021
Please also review video about Ultimate storage mode for writeback - Boost DAX Performance in Power BI with Dual Mode: by Guy in a Cube