topic Re: Query has exceeded available resources in DAX Commands and Tips

Query has exceeded available resources

MrBlueSky2 — Tue, 21 Jan 2025 13:45:04 GMT

Hi all,

I try to filter a table based on a field parameter value to show only the rows with margin below the selected value.

The regular filter visual does not allow to use a field parameter but only hard coded values.

So I tried to use a calculated column with a true/false based on the field parameter but it only compares to the default value, I assume it is assessed only at table load

I tried then to create a measure with true/false based on the field parameter however I get the error "the query has exceeded the available resources"

Even when I add the field parameter to the table I get this error, it is simply a scalar value so I don't understand why the engine is getting stuck with this. I even tried to create a measure with a constant value "1" and add it to the table and I get the same error.

Any help with this?

BR

Re: Query has exceeded available resources

Sahir_Maharaj — Tue, 21 Jan 2025 18:40:07 GMT

Hello MrBlueSky2,

Instead of calculating True/False for each row dynamically with a measure, you can rewrite your measure to focus only on filtering the rows:

Filter Measure = VAR SelectedMargin = SELECTEDVALUE(FieldParameterTable[FieldParameter]) RETURN IF( MAX(TableName[Margin]) < SelectedMargin, 1, 0 )

Re: Query has exceeded available resources

Kedar_Pande — Wed, 22 Jan 2025 10:23:27 GMT

MrBlueSky2

Create a Measure:

FilterMeasure = 
IF(
SELECTEDVALUE('YourFieldParameterTable'[FieldParameter]) > 0 &&
MAX('YourTable'[Margin]) < SELECTEDVALUE('YourFieldParameterTable'[FieldParameter]),
1,
0
)

Add this measure to your visual and set the filter to FilterMeasure = 1

If performance issues persist, simplify your data model or reduce the visual complexity by pre-aggregating data.

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn

Re: Query has exceeded available resources

MrBlueSky2 — Wed, 22 Jan 2025 21:20:41 GMT

Thanks for your help both, unfortunately suggested solutions don't work but I found a way to fix it. I created a measure with a IF that returns 1 or 0 based on the parameter and a recalculated margin within a SUMX instead of using the precalculated margin field.

It works now, the new formula is more complex but for some reason uses less resources

Re: Query has exceeded available resources

Anonymous — Thu, 23 Jan 2025 06:00:12 GMT

Hi MrBlueSky2

Thank you very much Kedar_Pande and Sahir_Maharaj for your prompt reply.

I'm glad to hear you found a solution! It sounds like using the SUMX function with a recalculated margin and an IF statement was the key. Sometimes, more complex formulas can indeed be more efficient, depending on how they interact with the data model and the engine's optimization.

You can accept it as a solution, which will help to help more people understand the problem.

Regards,

Nono Chen

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

Re: Query has exceeded available resources

Poojara_D12 — Thu, 23 Jan 2025 06:06:32 GMT

Hi MrBlueSky2

Please try using the below DAX:

IsBelowSelectedMargin = IF( MAX('Table'[Margin]) < SELECTEDVALUE('Field Parameter Table'[Field Parameter]), 1, 0 )

Reduce table size in Power Query.
Simplify DAX formulas.
Ensure correct relationships between tables.