Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Running total on a large scale dataset

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.
big data
DAX
Filter Table
running total

6 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

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