Forum Discussion
Can't create relationships between Snowflake tables when using DirectQuery or Composite Models
š I'm bringing in a few very, very small dimension tables via Import mode and a single small and overly simply Fact table via DirectQuery.
I can define the relationships, make them the appropriate cardinality (1:many on this textbook simple dim/fact model) and active, but when I try to create any visualization that uses a field from the dim table and fact table, I receive an "Error fetching data for this visual: OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.."
I've also tried to bring all the dim and fact tables in via DirectQuery and get the same result, however if I bring all the tables in via Import mode, I can succesfully utilize the data model and relationship to build visualizations, etc.
When utilizing DirectQuery or Composite models, do relationships simply not work in Power BI? That seems crazy. I know there are DAX and M limitations with DirectQuery, but this error seems to render DirectQuery utterly useless? Surely I'm doing something wrong!
Hi ketchum
This error typically occurs when Power BI tries to generate a SQL query that involves tables with different storage modes (Import and DirectQuery) and fails to perform query folding.In other words, it cannot efficiently combine data from an Import-mode dimension table with a DirectQuery fact table through SQL. This doesnāt mean relationships donāt work ā it just highlights the limitations of using Import and DirectQuery tables together in visuals, especially when Power BI canāt translate the visual into a valid backend SQL query.
You can try a work around: Use Dual storage mode instead of Import for your dimension tables
-
Right-click on the dimension table in the model
-
Select āStorage Modeā ā Choose Dual
This allows the table to behave as either Import or DirectQuery depending on the context of the query, which often resolves join/folding issues in visuals.
You can try Enable the Performance Analyzer in Power BI to inspect the generated query and understand whether the issue is in the visual, the measure, or the data model.
Let me know what happens!-
Hi ketchum,
Thank you for reaching out to Microsoft Fabric Community.
Thank you marcelsmaglhaes for the prompt response.
You cannot change an Import-mode table to Dual directly, please remove the dimension table and re add it as DirectQuery, and then change its storage mode to Dual. This allows power bi to optimize execution and resolve many folding and join issues.
- If you are using snowflake via ODBC, consider switching to the native Snowflake connector, it provides better support for DirectQuery and query folding.
- Also use single-directional relationships where possible, try using the basic one 1:many relationships, avoid bidirectional filtering unless necessary.
- Use Performance Analyzer, it helps to identify which part of the query is causing folding or performance issues.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" ā Iād truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
7 Replies
Hi ketchum
This error typically occurs when Power BI tries to generate a SQL query that involves tables with different storage modes (Import and DirectQuery) and fails to perform query folding.In other words, it cannot efficiently combine data from an Import-mode dimension table with a DirectQuery fact table through SQL. This doesnāt mean relationships donāt work ā it just highlights the limitations of using Import and DirectQuery tables together in visuals, especially when Power BI canāt translate the visual into a valid backend SQL query.
You can try a work around: Use Dual storage mode instead of Import for your dimension tables
-
Right-click on the dimension table in the model
-
Select āStorage Modeā ā Choose Dual
This allows the table to behave as either Import or DirectQuery depending on the context of the query, which often resolves join/folding issues in visuals.
You can try Enable the Performance Analyzer in Power BI to inspect the generated query and understand whether the issue is in the visual, the measure, or the data model.
Let me know what happens!Thanks for the response marcelsmaglhaes. I'll give the Performance Analyzer a try.
I can't select/change the Storage mode on my dim tables that are currently brought in via Import mode on the composite model, but I can change it for the fact table that is brought in via DirectQuery:
Oddly, I get the same behavior (unable to utilize relationships to build visuals) if I bring all the dim/fact tables in via DirectQuery mode, too. So anytime DirectQuery is involved, I'm seeing issues.
šµāš«
Just to show that I receive the same error when only utilizing DirectQuery:
I saw errors "earlier" in the process than at the visual / report level, though. When creating the relationships on the DirectQuery model, Power BI couldn't validate the relationship (I also changed the Cross-filter direction to both in an attempt to try anything that might work):
I didn't receive this error when everything was brought in via Import mode, it all just worked as-expected.
-
