Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
F5-Evan
New Member

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

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.

 

F5Evan_0-1696933497948.png

 

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.

6 REPLIES 6
Syndicate_Admin
Administrator
Administrator

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 ? 

 

v-stephen-msft
Community Support
Community Support

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
Regular Visitor

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
New Member

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
New Member

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
Administrator
Administrator

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors