Forum Discussion
Query has exceeded available resources
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
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 )
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
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.