Summing values of a column until a change occurs

[Anonymous]

Hello dear community.

I would like to know if anyone has an idea for a Dax formula. My wish would be that the values of this column are added until my ID changes. After each ID change, the values should be added back to zero. Furthermore, it would be helpful to have some kind of counting up to this change of ID. Even if the ID is repeated, as is the case here, it should start counting again after each change.

Below is an example of how it should look like. The DAX formulas should be able to output the two right columns like here:

I would be very grateful for some ideas, as I have already tried a few things without success.

[Anonymous]

Ok, I found out that I have less RAM memory available to run some formulas. Power BI gives the error message that there is "not enough RAM to perform this operation". So I changed the first two formulas a bit to work around this problem:

ChangeStatus = IF('Table'[ID]=LOOKUPVALUE('Table'[ID],'Table'[Index ID],'Table'[Index ID]-1),"","changed")

ChangeIndex = COUNTX ( FILTER ( ALL ('Table'), 'Table'[Index ID] <= EARLIER('Table'[Index ID]) && 'Table'[ChangeStatus] <> ""), 'Table'[Index ID])

Inspired by this page: https://community.powerbi.com/t5/Desktop/Reinit-count-when-value-changes/m-p/205258

Everything works wonderfully now. I really thank you very much for your solutions! 😃👍

But does anyone have an idea how to solve this RAM memory problem without physically upgrading the laptop or PC?

[Ashish_Mathur]

Hi,

Within every ID, in what order should the addition happen?  Is there a Date column somewhere?

[Anonymous]

There is an additional "Index ID column" as a sequential number. And a column with a date, but this is not in the order of the index.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  The data that you share should have a Date column.

[Anonymous]

Thank you very much for your answers. But the order of the date is not the same as the ID. What is decisive for me is the order of the ID. The "Number of something" is actually the days until the ID change. I have updated the example to show the problem. Is there a way to create the two columns "Count" and "Sum" from the others using DAX or something similar?

[v-easonf-msft · Accepted solution]

Hi， @Anonymous 

Try calculated columns as below:

flag_change = 
VAR ID1 = 'Table'[ID]
VAR ID2 =
    CALCULATE (
        MAX ( 'Table'[ID] ),
        FILTER ( 'Table', 'Table'[Index ID] = EARLIER ( 'Table'[Index ID] ) - 1 )
    )
RETURN
    IF ( ISBLANK ( ID2 ), 0, IF ( ID1 <> ID2, 1, 0 ) )   // change:1 unchange:0

New ID Index = 
CALCULATE (
    SUM ( 'Table'[flag_change] ),
    FILTER ( 'Table', 'Table'[Index ID] <= EARLIER ( 'Table'[Index ID] ) )
)

SUM = 
CALCULATE (
    SUM ( 'Table'[Number of something] ),
    FILTER (
        'Table',
        'Table'[New ID Index] = EARLIER ( 'Table'[New ID Index] )
            && 'Table'[Index ID] <= EARLIER ( 'Table'[Index ID] )
    )
)    //Group by 'New Id Index' to get the accumulated value

Count = 
CALCULATE (
    COUNTROWS('Table'),
    FILTER (
        'Table',
        'Table'[New ID Index] = EARLIER ( 'Table'[New ID Index] )
            && 'Table'[Index ID] <= EARLIER ( 'Table'[Index ID] )
    )
)

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @v-easonf-msft  for your suggested solution.

The first formula works fine. There is a small problem with the second formula "New ID Index". The actual file has over 100,000 rows. For the DAX formula it loaded for hours without showing a result, so I had to stop. Any idea how I could solve this problem? Or could the formula be split into two or three steps so that the PC is not overwhelmed?

Kind regards,

leovinc14

[Ashish_Mathur]

Hi,

In the second last row, why should the answer be 5?  Why should it not remain 1?  Also, please share data in a form that can be pasted in MS Excel.