Error : The query processor ran out of internal resources and could not produce a query plan

[Anonymous]

I receive the following error message : 

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.. The exception was raised by the IDbCommand interface.

My query is the following : 

LastClosed = CALCULATE(MAX('OptiQ Query'[ClosedUTC]),RELATEDTABLE('OptiQ Query'))

So i am trying to get the MAX date field from a related table.

both tables contain +10.000 rows. The formula worked in the past, but I assume that it fails since it passed a certain number of rows.

Any idea how I can make this work again?

Kind regards,

Bart

[Anonymous]

Mate, do you know what you are writing at all? I think you should do some serious reading about DAX. The fact that something "works" (for some time and for some scenarios) does not mean it's correct.

By the way, here's the documentation on RELATEDTABLE. It should only be used when there is context transition and some row context is active. This is not the case in your measure.

https://dax.guide/relatedtable/

You say "I'm trying to get the max date from a related table." Well, related to WHAT?

Best
D

[Anonymous]

Let me explain the situation in more detail.

Table ServiceOrder has a one to many relationship with workorderincident, workorderincident has a one to many relationship with tabel Query. (this relationship is actually 1 to 1, but due to null values, in the workorderincident table, this is impossible to select)

Table Query contains the send date.

I want to get the latest send date for each Service Order.

Therefor I used the CALCULATE(MAX('OptiQ Query'[SentUTC]),RELATEDTABLE('OptiQ Query')) Formula

I understand that because it worked in the past that is was correct. However this a forum to request help.

So I am requesting help to find the correct way to set this up.

Thanks for your friendly response.

[Anonymous]

Help is one thing, at least minimal understanding of what you do is yet another. It always pays off to know what you do and why. Especially with DAX.

For your own good you should read about DAX since your "measure" shows without fail you don't get it at all. You might not like what you read, it's OK, I don't blame you. The important bit is that you start reading on DAX. Then my job is done.

As for help... All you most likely need is this:

[Latest Send Date] = MAX('OptiQ Query'[SentUTC])

Yes, it's that simple.

If your tables are set up correctly, then this is THE measure. If not, then... please try to start get familiar with what it means "correct model."

And don't forget to check out the docs on RELATEDTABLE.

All the best
D

[Anonymous]

I understand what related table is and what it does.

And no it's not that simple.

If I use a normal MAX, like you sugested, I get the max date in the workorderincident table. I need the max date of the related records not of the whole table. It seems the MAX is not checking for related records (although there is a 1 to many-1 service order multuple workorderincidents, relationship between both tables).

Therefor my question is :

- what are the limitations of related table?

- is their a more sutable solution for getting this data?

[Anonymous]

You're not talking about a measure then but about a calculated column, right? In the post you did not say that 😞 There's a huge difference between a measure and a calculated column.

For a calc column it's this:

[Latest Date] = // calculated column
maxx(
relatedtable( 'OptiQ Query'),
'OptiQ Query'[SentUTC]
)

Next time, please be as specific as possible about what you want.

Best
D

[Anonymous]

If you don't want to have context transitions (which are very expensive if your table in which the column is is huge), then you'll need to do something different, more complex. But to write code without context transition I have to know the model you have.

Best
D

[Anonymous]

It might be that you're getting the error because there are TOO MANY CONTEXT TRANSITIONS. Then you don't have a choice - you have to rewrite the formula to only use FILTER without any CALCULATE or RELATEDTABLE (which does context transition as well).

Best
D

[Anonymous]

Hi Darlove,

thanks for the answers.

I will look in to all possible improvements (calculated columns,context transitions, filter,...)

I'll let you know how I solved it, or if I need extra info later this week.

Thanks! 

[Anonymous]

OK. Please do.

But first try this simple formula:

[Latest Date] = // calculated column
maxx(
relatedtable( 'OptiQ Query'),
'OptiQ Query'[SentUTC]
)

Best
D

[Anonymous]

Quick update :

For now, i have found a solution.

i was using related table for a table that had no direct link with my main table, there was a table in between those 2.

The temporary solution was creation a new field on the middle table which used related table to the 3th table. 

And then updating the column on the first table to link to the second one instead of useing relatedtable to the 3th.

So that woks, for now, but my next step is to still read up upon all possible solutions because i feel that a more permanent/better solution is possible/needed.

Thanks for your help!