Reports don't work post deploying from DEV to UAT using Deployment Pipeline

[PRAVISH_SARAVAN]

Visuals throw below error post deploying them to UAT but everything works fine in DEV.

Expression.Error: We couldn't fold the expression to the data source. Please try a simpler expression.. ;We couldn't fold the expression to the data source. Please try a simpler expression.. The exception was raised by the IDbCommand interface.

This was not there till last week, everything were smooth but all of a sudden getting such error.

Please check and do the needful.

[Anonymous]

Hi @PRAVISH_SARAVAN ,

The error message you provided indicates that the expression you’re using is too complex for Power BI to fold to the data source. This error is common when using Direct Query mode in Power BI.

To fix this error, you can try the following:

• Simplify your expression by removing any unnecessary complexity.
• Avoid using complex transformations in the Power Query Editor.
• Ensure that your data model is as simple as possible.
• Implement as much of the logic for your calculations in your data source and reduce the amount of DAX you need to write.

If the error persists, you can try writing your DAX in a different way in the hope that Power BI will be able to fold it.

For more details, you can read related document: Chris Webb's BI Blog: Understanding The “We Couldn’t Fold The Expression To The Data Source” Error I...

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PRAVISH_SARAVAN]

Thanks for the response. But the strange worry is,it's working smoothly in DEV workspace but why the issue raises in the TEST workspace alone post deployment.

If I publish directly from DESKTOP(Pointing DEV workspace) it's working but this issue occurs when I deploy to TEST workspace or any new workspace using PBI DEPLOYMENT PIPELINE.

So I'm confused on this part.

Also the calculcation used is normal CALCULATE(<AGGREGATE>,<FILTERs>) format

[Brunner_BI]

@PRAVISH_SARAVAN the first thing to understand here is that query folding happens in your Power Query, it has nothing to do with any calculations or DAX, measures etc.

Meaning that something in your backend/PQ breaks the DirectQuery.

Did you define some parameters there that might have changed when you pushed to UAT?

You should download whatever you have in UAT and analyze the issue in Power BI Desktop, because exactly that file cannot execute the direct query.

What I am guessing the problem is is that your UAT report was automatically binded to a different dataset. Do you have another pipeline for your datasets?

The error you are mentioning is quite weird though, only one visual is throwing this? Can you provide a screenshot?

[PRAVISH_SARAVAN]

I do agree the Query folding happens in the Power Query Editor and we have not applied any query folding in our Power Query Editor.

Yes we have connection details paramters defined in the Dataflows, pointing to these Dataflows the common Dataset(Semantic Model) is created.

Inside dataset we perform basic transformation like join, append, remove or add columns.

And no, as one workspace can be assigned only once, we have used this workspace in only one pipeline. But we do have different pipeline for different workspaces.

The issue is not only for one visual, it's happening for various visuals except the places where Python script visuals is used.

visual breaking here are normal TABLE visual

[Brunner_BI]

So if you download the report in UAT you can confirm it is connected to the dataset you expect it to be connected to?

Generally this seems to be hitting the limitations of DirectQuery by what you are doing inside the report, maybe month ending is a calculated column or so.

Make sure to read through the limitations and then analyze what could be the cause. It could be your data model or a calculated column or something with Auto Date/Time?

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery#other-directquery-li...

[PRAVISH_SARAVAN]

sure, will go through the shared link on DirectQuery Limitations.
Also, the MonthEnding is not a calculcated column, it's pulled form the created Dataflow.

The strange reason was, until last friday(3/11/2023), the reports and deployments were wokring fine, starting this monday(6/11/2023) the reports started to not load the visual in TEST workspace. 

Was there any new release happened over last weekend? Could that be possible route cause, if so any release udpate we can expect this weekend?