Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi all,
I'm having a 80 million record dataset. When I was doing the POC report that has only account number, I used the following fomular to generate my balance:
We have a table that has all client's transaction since they become our client. Sometimes, people would ask us to generate a statement of account for a particular client.
how does that require a running total? And how fast does it have to be?
Hi, @xieli1999
An error message indicating resource exhaustion can be due to the complexity and size of the dataset processed by the formula. Power BI has certain limitations when working with large datasets, especially when it comes to memory usage.
Use variables to break down formulas into smaller, manageable pieces. This improves readability and performance. Ensure that their use is absolutely necessary and cannot be simplified.
The problem with creating a filter table that returns an empty can be due to the way the function is used. When there is a value in the specified column in the current context, a value is returned; Otherwise, it will return a blank. This may mean that your slicer or filter context doesn't have a single selected account at the time of calculation
Make sure that the slicer or filter settings allow for a single selection, or that the filter context that returns a non-null value is applied correctly.
Temporarily replace with a hard-coded account number (as you've done) to confirm that the rest of the formula is working as expected. This helps to isolate the problem to the dynamic selection part of the formula.
If possible, review and optimize the performance of your data model. This could include reducing the number of columns, ensuring proper indexing, or breaking down data into smaller, more manageable tables.
For further optimization techniques and to understand limitations, see the following resources:
Optimization guide for Power BI - Power BI | Microsoft Learn
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @xieli1999
Please try
Balance =
VAR OpenBslsnce = [Opening balance]
VAR IndexAmount =
ADDCOLUMNS (
CALCULATETABLE ( VALUES ( transaction[Index] ), ALLSELECTED () ),
"@Amt",
CALCULATE (
SUM ( transaction[Amount] ),
ALLEXCEPT ( transaction, transaction[Index] )
)
)
VAR Amount =
SUMX (
WINDOW ( 0, abs, 0, rel, IndexAmount, ORDERBY ( transaction[Index], ASC ) ),
[@Amt]
)
RETURN
OpenBalance + Amount
Thans tamerj1 for your solution, unfortunately when I applied it on a 20 milliaon record table, it's again running out of resources and my actual table has over 80 million records.
a running total over 80 million records seems a bit excessive. What is the business question you are trying to answer?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
41 | |
27 | |
23 | |
22 | |
22 |