topic Re: Running total on a large scale dataset in DAX Commands and Tips

Running total on a large scale dataset

Anonymous — Thu, 28 Mar 2024 19:03:09 GMT

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:

Balance = [Opening balance] +

CALCULATE(

SUM(transaction[Amount]),

FILTER(

ALLSELECTED(transaction),

[Index] <= MAX(transaction[Index])

)

The above fomular works exactly as what I want. However, when I load not all my data, but just over 10 million data, this fomular gives me an error message saying that it's running out resources.

Then I thought about dividing my original transaction table into pieces first as for my report, anyway, one account number and only one account number must have been selected. I used the following fomular to divide my table:

FilteredTable =

CALCULATETABLE(

'transaction',

FILTER (

'transaction',

'transaction'[Account_number] = SELECTEDVALUE('transaction'[Account_number])

)

It gives me an empty table, if I replaced "SELECTEDVALUE('transaction'[Account_number])" by a fixed text string, new table will be created with data.

Anybody can help me on this issue?

Thanks in advance.

Re: Running total on a large scale dataset

tamerj1 — Thu, 28 Mar 2024 21:28:58 GMT

Hi Anonymous

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

Re: Running total on a large scale dataset

Anonymous — Tue, 02 Apr 2024 03:46:08 GMT

Hi, Anonymous

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.

Re: Running total on a large scale dataset

Anonymous — Wed, 03 Apr 2024 18:04:42 GMT

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.

Re: Running total on a large scale dataset

lbendlin — Wed, 03 Apr 2024 20:07:52 GMT

a running total over 80 million records seems a bit excessive. What is the business question you are trying to answer?

Re: Running total on a large scale dataset

Anonymous — Wed, 03 Apr 2024 20:09:44 GMT

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.

Re: Running total on a large scale dataset

lbendlin — Wed, 03 Apr 2024 20:11:38 GMT

how does that require a running total? And how fast does it have to be?