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
I have this issue on a direct query data source (postgresql connection + SQL Query in the data source connection).
I also tried to create the view on the data ource and just connect to the view.
I continue to have this folding error again and again on only one column… I’m totally stuck 🙁
https://community.fabric.microsoft.com/t5/Desktop/We-couldn-t-fold-the-expresion-to-the-data-source-NO-DAX/m-p/3638179#M1191369
Our team had a terrible problem with the “We couldn’t fold the expression…” error while connecting to our Snowflake warehouse, and found quite the odd solution.
We made one query to a Snowflake table (without transformation) via DirectQuery, and would get the “We couldn’t fold the expression…” error for even the simplest of requests in PowerBI: such as Count of [ID], by [Group ID]. When this happened, there wouldn’t be any record of a failed query in our Snowflake admin panel.
What we found through trial and error was that this table contained other columns – neither [ID] nor [Group ID] – that were array-type, and their presence in the PowerBI query was enough to cause the request to fail (despite the array columns not being referenced in the request). All our problems were solved when we removed the array-type columns via the Power Query Editor.
Interesting, I hope I had the same issue, but I’m getting the error using Snowflake when I’m just trying to filter a Card 🙁 Nothing fancy, no complex DAX formulas, just a CARD with the average of a number (fine until here) and crashing when adding a simple filter (true/false).
You saved my life! I was going nuts trying to figure out why this error was appearing in the simplest of visuals (sum of X group by Y).
You’d think Microsoft would include something like this in their docs, right? Like any sensible human would :\
[Version = “1.0.0”]
section testconnector;
// Define the icons for the connector
testconnectorIcons = [
Icon16 = {
Extension.Contents(“testconnector16.png”) },
Icon32 = {
Extension.Contents(“testconnector32.png”) }
];
[DataSource.Kind=”testconnector”, Publish=”testconnector.Publish”]
shared testconnector.Contents = (dsn as text) =>
let
connectionString = “dsn=” & dsn & “;”,
NavigationTable = Odbc.DataSource(connectionString, [
HierarchicalNavigation = true
]),
SqlCapabilities = [
SupportsDirectQuery = true,
SupportsFold = true, // Enable query folding
SupportsTopN = true, // Ensure top N is supported
SupportsNumericLiterals = true, // Support numeric literals
SupportsStringLiterals = true, // Support string literals
SupportsLogicalLiterals = true, // Support logical literals
SupportsJoin = true, // Support join operations
SupportsInnerJoin = true, // Support inner joins
SupportsOuterJoin = true, // Support outer joins (left, right, full)
SupportsGroupBy = true, // Support group by operations
SupportsOrderBy = true, // Support order by operations
SupportsSubquery = true, // Support subqueries
SupportsDistinct = true // Support distinct keyword
]
in
NavigationTable;
testconnector = [
TestConnection = (dataSourcePath) => {“testconnector.Contents”, dataSourcePath},
Authentication = [
Anonymous = []
],
Label = Extension.LoadString(“DataSourceLabel”)
];
testconnector.Publish = [
Beta = true,
Category = “Other”,
ButtonText = { Extension.LoadString(“ButtonTitle”), Extension.LoadString(“ButtonHelp”) },
LearnMoreUrl = “https://powerbi.microsoft.com/”,
SourceImage = testconnectorIcons,
SourceTypeImage = testconnectorIcons,
SupportsDirectQuery = true,
SupportsFold = true
];
The export testconnector.Contents was exported multiple times.
what does this means ?
And I Get same error at BI we couldn’t..
Which connector are you trying to use here?