The query has exceeded available resources, optimization needed

Hi wanglibin,

Why you get “query exceeded resources”?

Your measure 4 is very expensive because:

• It filters on another measure ([%])
• Uses FILTER(ALLSELECTED())
• Runs row-by-row inside a visual

That causes nested scans of the same table = memory explosion.

Correct & optimized approach (single measure, no % column)
Goal: Find the lowest price where cumulative quantity ≥ 50% of total

 

Optimized Measure (SAFE & FAST):

---DAX---
50% Vol Price =
VAR TotalQty =
CALCULATE (
SUM ( 'Summarized POS'[Resale Qty] ),
ALLSELECTED ( 'Summarized POS' )
)

VAR TargetQty = TotalQty * 0.5

VAR PriceTable =
ADDCOLUMNS (
VALUES ( 'Summarized POS'[Price$] ),
"CumQty",
CALCULATE (
SUM ( 'Summarized POS'[Resale Qty] ),
FILTER (
ALLSELECTED ( 'Summarized POS' ),
'Summarized POS'[Price$]
<= EARLIER ( 'Summarized POS'[Price$] )
)
)
)

RETURN
MINX (
FILTER ( PriceTable, [CumQty] >= TargetQty ),
'Summarized POS'[Price$]
)
---DAX---

Why this works?

• No filtering on measures
• No circular logic
• Only one table scan
• Uses price grain, not row grain

This is the standard weighted median pattern in Power BI.

 

What to remove:

• Measure 2, 3, 4
• % measure
• FILTER(ALLSELECTED(), [%] >= 0.5)

Always Remember:

• Never filter a table using another measure
• Compute cumulative logic inside one controlled virtual table

=================================================================
Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Jaywant Thorat | MCT | Data Analytics Coach | Super User
LinkedIn: https://www.linkedin.com/in/jaywantthorat/
Join #MissionPowerBIBharat: https://tinyurl.com/JoinMissionPowerBIBharat
#MissionPowerBIBharat
LIVE with Jaywant Thorat

I am not exactly sure about your data but it is obvious that you’re hitting the limit because measure 4 filters a large table using another measure. Instead that you could use a single measure as below logic:

50% Vol Price =
VAR TotQty =
    SUM ( 'Summarized POS'[Resale Qty] )

VAR PriceQty =
    SUMMARIZE (
        'Summarized POS',
        'Summarized POS'[Price$],
        "Qty", SUM ( 'Summarized POS'[Resale Qty] )
    )

VAR WithCum =
    ADDCOLUMNS (
        PriceQty,
        "CumQty",
            VAR p = [Price$]
            RETURN
                SUMX ( FILTER ( PriceQty, [Price$] <= p ), [Qty] )
    )
RETURN
MINX (
    FILTER ( WithCum, DIVIDE ( [CumQty], TotQty ) >= 0.5 ),
    [Price$]
)