Forum Discussion
“Query has exceeded the available resources” in Power BI
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.
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 )
)
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.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.