If you’re using DirectQuery mode in Power BI you may occasionally run into the following error message:
Couldn’t load the 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..
What does it mean and how can you fix it?
To understand what’s going on here you must first understand what query folding is. There’s some great documentation here that I strongly recommend you read, but in a nutshell query folding refers to how the Power Query engine inside Power BI can push calculation and transformation logic back to whatever data source you’re using in the form of a query – for example a SQL query if your data source is a relational database. Most of the time when people talk about query folding they are using Import mode but it’s even more important in DirectQuery mode: in DirectQuery mode not only does every transformation you create in the Power Query Editor have to fold, but every DAX query (including all your DAX calculations) generated by the visuals on your report has to be folded into one or more queries against your data source too.
You can do some pretty complex things in the Power Query Editor and in DAX and the error message above is the error you get when Power BI admits defeat and says it can’t translate a DAX query generated by a visual on a report into a query against your data source. The cause is likely to be a combination of several of the of the following:
- A complex data model
- Complex DAX used in measures or calculated columns
- The use of dynamic M parameters
- Complex transformations created in the Power Query Editor
Unfortunately it’s hard to be more specific because Power BI can fold different transformations to different data sources and this error almost never occurs in simple scenarios.
How can you avoid it? Again, I can only offer general advice:
- Don’t do any transformations in the Power Query Editor if you’re using DirectQuery mode. If you want to use DirectQuery you should always make sure your data is modelled appropriately in whatever data source you’re using before you start designing your dataset in Power BI.
- Keep your data model as simple as possible. For example, avoiding bi-directional relationships is a good idea.
- Try to implement as much of the logic for your calculations in your data source and reduce the amount of DAX you need to write.
- Try to write your DAX in a different way in the hope that Power BI will be able to fold it.
What I find painful, is that ANY fault in your custom sql query in import mode when folding is on, will also result in this error. Instead of telling you your query can’t be run because it is syntactically incorrect, it tells you stuff about query folding which is not relevant at that moment.
So perhaps you need an extra bullet point: “the custom query you wrote has errors in it”
Hi Chris – We recently started getting this error on existing Dataflows that use Direct Query. When we try to make a new connection, the Direct Query option is no longer available, it defaults to Import mode. However, this is only an issue when working on the compony network/firewall. When working from home, outside the network, Direct Query on these dataflows works as expected.
Our InfoSec team tells me nothing changed on their end and I don’t see anything in the Admin settings that would prevent users from using Direct Query. Any idea how I can trouble shoot this? I’m at a loss and we’ve been using Direct Queries on dataflows for a couple years now.
I think you need to open a support case to get this properly investigated