Solved: The query has exceeded available resources, optimi...

wanglibin · ‎02-04-2026

Hello, I am trying to calculate medium number of price with weighting to resale quantity, example as below:

Price	Quantity	%	Price Chosen
1	100	10%
1.2	500	60%	%>=0.5, Price=1.2
1.4	300	90%
1.5	100	100%

However, i encounter query exceeded available resources, i know the issue is from measure 4, but i don't know how to optimize it

or any other solution to get the same result.

measure 1

Total Qty =

VAR _Product=ALLSELECTED('Summarized POS'[Product])

VAR _Month=ALLSELECTED('Summarized POS'[ST Month])

RETURN

CALCULATE(sum('Summarized POS'[Resale Qty]),

ALL('Summarized POS'),

'Summarized POS'[Product] in _Product,

'Summarized POS'[Month] in _Month,

VALUES('Summarized POS'[Product]))

measure 2

Accu.Qty = CALCULATE(

sum('Summarized POS'[Resale Qty]),

values('Summarized POS'[Product]),

filter(ALLSELECTED('Summarized POS'),'Summarized POS'[Price$]<=max('Summarized POS'[Price$])))

measure 3

% = DIVIDE([Accu.Qty],[Total Qty])

measure 4

50% Vol Price$ = CALCULATE(min('Summarized POS'[Price$]),VALUES('Summarized POS'[Product]),FILTER(ALLSELECTED('Summarized POS'),'Summarized POS'[%]>=0.5))

thank you for your help!

Jaywant-Thorat · ‎02-05-2026

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

View solution in original post

cengizhanarslan · ‎02-05-2026

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$]
)

_________________________________________________________
If this helped, ✓ Mark as Solution | Kudos appreciated
Connect on LinkedIn | Follow on Medium
AI-assisted tools are used solely for wording support. All conclusions are independently reviewed.

View solution in original post

cengizhanarslan · ‎02-05-2026

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$]
)

_________________________________________________________
If this helped, ✓ Mark as Solution | Kudos appreciated
Connect on LinkedIn | Follow on Medium
AI-assisted tools are used solely for wording support. All conclusions are independently reviewed.

v-menakakota · ‎02-08-2026

Hi @wanglibin ,

Thanks for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank  @cengizhanarslan , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

Best Regards,
Community Support Team

v-menakakota · ‎02-11-2026