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.