“Query has exceeded the available resources” in Power BI

[Raja10ram]

Hi everyone,

I’m facing a performance issue in Power BI where the report throws the error “Query has exceeded the available resources” while loading visuals. we are using this DAX Calculation.

Opening Balance =

CALCULATE (SUM ( 'Ledger'[DBAMOUNT] ) - SUM ( 'Ledger'[CRAMOUNT] ),

    WINDOW (1,ABS,-1,REL,

            ALL ('Ledger'[date],'Ledger'[narriation],'Ledger'[voucher no]),

        ORDERBY ('Ledger'[date],1,'Ledger'[voucher no], 1)

    )

)

 

[v-hjannapu]

Hi @Raja10ram,

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 wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[v-hjannapu]

Hi @Raja10ram,
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 are always here to support you.


Regards,
Community Support Team.

[cengizhanarslan]

Create a numeric PostingIndex in Power Query / source (date + voucher sequence), then:

Opening Balance =
VAR CurIdx = MAX ( 'Ledger'[PostingIndex] )
RETURN
CALCULATE (
    SUM ( 'Ledger'[DBAMOUNT] ) - SUM ( 'Ledger'[CRAMOUNT] ),
    FILTER ( ALL ( 'Ledger'[PostingIndex] ), 'Ledger'[PostingIndex] < CurIdx )
)

[Raja10ram]

Still we getting the same issue while we are using the above logic. @cengizhanarslan 

[v-hjannapu · Accepted solution]

Hi @Raja10ram,

Thanks for the update.
Since the same error is still coming even after using the PostingIndex logic, this looks like a data volume and visual-level issue rather than a DAX syntax problem. The opening balance is getting calculated for every row shown in the visual, and when the ledger table is large or when the visual contains columns like date, voucher number, and narration together, Power BI has to scan too many rows and it runs out of resources. This usually happens in Pro or shared capacity also. For large ledger data, calculating opening or running balance using DAX at transaction level is not recommended. The better and stable approach is to calculate the running opening balance in Power Query or at the source after sorting by date and voucher, and then use it directly in the report. This should avoid the resource error.

Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Regards,
Community Support Team.

[v-hjannapu]

Hi @Raja10ram,
I hope the information shared above helps resolve your issue. If you still need any assistance or have more questions, please feel free to contact us. We are here to help.

Regards,
Community Support Team.