cancel
Showing results for
Did you mean:
Helper III

## Conditional Cumulative Calculated Column

Hi, I would like to create a cummulative counting column that adds 1 when [rsup] becomes equal to "0". The process order should follow [date] (like in the example below, descending). Once the column becomes "0", the [Goal Column] should equal "0". Once it changes to anything but a "0", it should add 1 to the existing [Goal Column] value.

As you can see, there are multiple [Well] categories, so it should be filtered on this.

Thank you and let me know if you need an additional description of the problem! I really appreciate your help, I've been stuck with this for a while.

 Well date rsup Goal Column justin 6/1/2021 5:00 7 1 justin 6/1/2021 5:10 7 1 justin 6/1/2021 5:20 7 1 justin 6/1/2021 5:30 1 1 justin 6/1/2021 5:40 7 1 justin 6/1/2021 5:50 0 0 justin 6/1/2021 6:00 7 2 justin 6/1/2021 6:10 7 2 justin 6/1/2021 6:20 0 0 justin 6/1/2021 6:30 7 3 justin 6/1/2021 6:40 7 3 brett 6/4/2021 2:10 7 1 brett 6/4/2021 2:20 7 1 brett 6/4/2021 2:30 0 0 brett 6/4/2021 2:40 7 2 brett 6/4/2021 2:50 1 2 brett 6/4/2021 3:00 0 0 brett 6/4/2021 3:10 7 3
3 REPLIES 3
Super User

Hi, @Jkilb

step one CC =
VAR currentwell = 'Table'[Well]
VAR currentdatetime = 'Table'[date]
VAR previousdatetime =
CALCULATE (
MAX ( 'Table'[date] ),
FILTER (
'Table',
'Table'[Well] = currentwell
&& 'Table'[date] < currentdatetime
)
)
RETURN
SWITCH (
TRUE (),
'Table'[rsup] = 0, 0,
CALCULATE (
SUM ( 'Table'[rsup] ),
FILTER (
'Table',
'Table'[Well] = currentwell
&& 'Table'[date] = previousdatetime
)
) <> 0, 0,
1
)

Goal Column CC =
VAR currentwell = 'Table'[Well]
VAR currentdatetime = 'Table'[date]
VAR previousdatetime =
CALCULATE (
MAX ( 'Table'[date] ),
FILTER (
'Table',
'Table'[Well] = currentwell
&& 'Table'[date] < currentdatetime
)
)
RETURN
IF (
'Table'[rsup] = 0,
0,
SUMX (
FILTER (
'Table',
'Table'[Well] = currentwell
&& 'Table'[date] <= currentdatetime
),
'Table'[step one CC]
)
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Helper III

Thank you for the quick response and support - however, after running the first formula, I'm receiving the following error:

"There's not enough memory to complete this operatino. Please try again later whne there may be more memory avilable".

My dataset currently has ~25 million rows. Is this something I should do in the query editor? Perhaps with Python or M?

Thanks!

Community Support

Hi @Jkilb

You could consider reducing the data model size by removing some unnecessary rows/columns in Power Query Editor. There are some tips about how to reduce data model size for your reference:

3 ways to REDUCE DATA In Power BI Desktop (No Premium needed!) - YouTube

Top 5 tips to reduce your Power BI data model size — Apex Insights: Power BI tips & tricks

Data reduction techniques for Import modeling - Power BI | Microsoft Docs

Or you can use a subset of data to test the formulas when you create a report.

Regards,
Community Support Team _ Jing

Announcements

#### Launching new user group features

Learn how to create your own user groups today!