Query has exceeded available resources

[MrBlueSky2]

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

[Poojara_D12]

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.

 

[Kedar_Pande]

@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

[Sahir_Maharaj]

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 )

[MrBlueSky2 · Accepted solution]

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

[v-nuoc-msft]

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.