Too many literal binary expressions in the query. The maximum number is 200

[F5-Evan]

I have a Power BI report grabbing  data from dataverse and have found a few of my visuals returning the error above:

"Too many literal binary expressions in the query. The maximum number is 200"

the visuals have been working for over a year and only broken recently.

The visual itself is a table and has about 11 columns, 9 of which are measures  which are generally just summing table columns.

I've tried to see if it was a specific column by deleting a column from the visual one at a time, but it doesn't seem to be a specific column and only starts to 'work' when a majority of them are deleted.

There isn't a large amount of rows in the table either, there should only be up to 530 rows.

Any help would be appreciated.

[fredrikgrondahl]

Hi, 

I got this error message, which seems kind of similiar: 

After some testing, it's obvious that the error is caused by direct queries. The imported tables worked perfectly fine. My temporarely solution was to delete the direct query-tables and add them again.

[Syndicate_Admin]

Same issue here with a report than ran for more than 6 months without issue. Last week the report used to work on the Desktop and failed when published. This week, it fails everywhere (even on a premium workspace). Could it be a side effect of the last two upgrades ? 

[Anonymous]

Hi @F5-Evan ,

Sorry to hear that you faced the issue.

To resolve this issue. Here are a few suggestions to achieve this:

a. Simplify your measures: - Review the measures in your report and identify if there are any complex calculations or unnecessary binary operations. - Simplify the measures by removing any unnecessary binary operations or by combining multiple measures into a single measure where possible.

b. Use calculated columns instead of measures: - If your measures involve complex calculations or multiple binary operations, consider converting them into calculated columns. - Calculated columns are pre-calculated and stored in the data model, which can help reduce the number of literal binary expressions in the query.

c. Limit the number of visuals on a single page: - If you have multiple visuals on a single page, each visual adds to the overall query complexity. - Consider splitting your visuals across multiple pages or reports to reduce the number of literal binary expressions in each query.

d. Optimize query performance: - Review the query performance of your report to identify any areas that can be optimized. - Consider using query folding techniques to push the data transformation operations back to the data source, reducing the number of literal binary expressions in the query.

e. Limit the number of rows returned: - If your table has a large number of rows, consider applying filters or aggregations to limit the number of rows returned in the query. - This can help reduce the overall query complexity and the number of literal binary expressions.

g. Test with a smaller dataset: - Create a test version of your report with a smaller dataset, such as a subset of the original data. - This can help isolate the issue and identify if it is related to the size of the data or specific calculations.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[matthiashh]

Same issue. I also have a composite model due to the fact that this is a demo for integrated interactive commenting. It has an integrated PowerApp to enter comments that are stored in a DataVerse table and should occur in a timely manner.
That worked fine until at least 2 months ago.
I didn't accessed the demo since then and I did not changed any of it nor of the components it depends on. It was just not used in the meantime.

So this error message should be caused by changes in the platform.
The same error occurs when opening the pbix file locally with Power BI desktop.

[F5-Evan]

We did some debugging and found that the issue is that we had a visual with columnns from both a direct query table and an import table.

To further explain, the issue is that Power BI tries to generate a query for the server to load but it due to the joins it has to do (compounded by the amount of columns and rows returned) it will get to a point where the server can't/won't load it.

To see this, you can use the query diagnostics tool in Power BI desktop to see what the query to the server looks like (https://learn.microsoft.com/en-us/power-query/query-diagnostics).

The only options to fix this (at least from our perspective):

• Add filters for the table and prolong the issue 
• Change the direct query table(s) to import

[finike]

I'm facing the exact same issue. I've used composite model now half a year and all of the sudden "Too many literal binary expressions in the query. The maximum number is 200"- message appeared.

My composite model does use relationship keys that generates way over 200 lines in "where / in"- clauses, but still I haven't had this issue earlier. Calculations are very easy and the actual visual does not have that much fields.

Has there been new updates or enforcement of limitations or what?

[Syndicate_Admin]

I am facing the same issue on my side. Small table (87 lines), a few measures (7 only), and a simple relationship with a date table, and I get this error when I try creating a simple chart with year/month in Y and a measure in X.