Forum Discussion

MP-iCONN's avatar
MP-iCONN
Resolver I
4 years ago
Solved

Internal error - An expression services limit has been reached

I ran into a recent issue with a visual I am creating.  

 

The Error Message is:
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.. The exception was raised by the IDbCommand interface.

 

Now I believe I have know the reason why it is occuring (due to a column I created) but unsure how to fix.

 

Basically I have to custom columns.  One called Neg Col will check for all on hand quantities of an item and subtract from the backlog quanity.   This will result in a negative or positive number.

 

Neg col =
RELATED('Item'[IMA_OnHandQty]) - 'Sales Orders'[Backlog Qty]
 
Now if I just add that Neg Col this works but once I add my next column it gives that error.
 
This colum is called Enough Col as seen below. 
 
Enough Col =
IF([Neg col] >= 0,"Yes","No")
 
Now if the Neg Col is greater than or equal to Zero it puts Yes, if not it will put a No.
 
For some reason that Enough Col when added prevents the visual from showing.
 
Any idea? 
 
Thank you for any help on this!

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi MP-iCONN ,

     

    To work around this issue, rewrite your query. Reference fewer identifiers and constants in the largest expression in the query. You must make sure that the number of identifiers and constants in each expression of the query does not exceed the limit. To do this, you may have to break down a query into more than one single query. Then, create a temporary intermediate result.

     

    You may try add the Neg Co /Enough Col column in Power Query by using "Merge"...

     

    Refer to:

    https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-8632-database-engine-error?view=sql-server-ver15

     

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi MP-iCONN ,

     

    To work around this issue, rewrite your query. Reference fewer identifiers and constants in the largest expression in the query. You must make sure that the number of identifiers and constants in each expression of the query does not exceed the limit. To do this, you may have to break down a query into more than one single query. Then, create a temporary intermediate result.

     

    You may try add the Neg Co /Enough Col column in Power Query by using "Merge"...

     

    Refer to:

    https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-8632-database-engine-error?view=sql-server-ver15

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi MP-iCONN ,

     

    Could you tell me if my post helps you a little? If it is, kindly Accept it as the solution to make the thread closed. More people will benefit from it.

     

    Best Regards,
    Eyelyn Qin