DAX calculated column to get running sum which resets on particular conditions

[mahenkj2]

I need help in writing a DAX calculated column to get running sum which resets on particular conditions. sample data is as below:

Created Date                 Index    clean trigger  machine    type    counter      Comments

05/01/2019 15:06:47	1	1	1	1	1	clean trigger + type change resets the counter to 1
05/01/2019 17:38:16	2	0	1	0	1	counter is maintained at previous calculation when trigger and type are 0
05/01/2019 22:01:27	3	0	1	0	1
06/01/2019 03:13:07	4	0	1	0	1
06/01/2019 06:44:09	5	0	1	0	1
06/01/2019 08:02:39	6	0	1	0	1
06/01/2019 10:58:31	7	0	1	1	2	alone change in type triggers increment in counter
06/01/2019 11:32:06	8	0	1	0	2
06/01/2019 14:48:30	9	0	1	0	2
06/01/2019 19:26:21	10	0	1	0	2
06/01/2019 21:07:37	11	1	1	1	1	clean trigger + type change resets the counter to 1
06/01/2019 22:01:41	12	0	1	1	2
06/01/2019 23:09:44	13	0	1	0	2
07/01/2019 02:03:59	14	0	1	1	3	alone change in type triggers increment in counter
07/01/2019 02:37:54	15	0	1	0	3
07/01/2019 07:27:37	16	0	1	0	3
07/01/2019 12:39:22	17	1	1	1	1
07/01/2019 13:20:19	18	0	1	0	1
07/01/2019 13:42:59	19	0	1	0	1
07/01/2019 17:00:16	20	0	1	1	2
07/01/2019 17:20:32	21	0	1	0	2
07/01/2019 22:25:01	22	0	1	0	2
08/01/2019 05:07:33	23	0	1	0	2
08/01/2019 05:12:25	24	0	1	0	2
08/01/2019 08:46:19	25	0	2	1	1	now there is machine change also present, trigger is not present
08/01/2019 09:02:42	26	0	2	0	1
08/01/2019 10:18:31	27	0	2	0	1
08/01/2019 15:29:39	28	0	2	0	1
08/01/2019 18:29:02	29	0	2	0	1
08/01/2019 19:16:47	30	0	2	0	1
08/01/2019 19:46:35	31	0	2	1	2
08/01/2019 22:14:24	32	0	2	0	2
08/01/2019 22:50:52	33	0	2	0	2
09/01/2019 05:33:32	34	0	2	0	2
09/01/2019 09:04:43	35	1	2	1	1
09/01/2019 10:03:37	36	0	2	1	2
09/01/2019 10:44:10	37	0	2	0	2
09/01/2019 14:10:18	38	0	2	1	3
09/01/2019 14:38:07	39	0	2	0	3
09/01/2019 20:47:29	40	0	2	0	3
10/01/2019 00:18:21	41	1	2	1	1
10/01/2019 01:07:44	42	0	2	0	1
10/01/2019 01:30:54	43	0	2	0	1
10/01/2019 06:15:20	44	0	2	1	2
10/01/2019 10:01:45	45	0	2	0	2
10/01/2019 14:40:09	46	0	2	0	2
10/01/2019 16:10:05	47	0	2	0	2
10/01/2019 18:51:48	48	0	2	0	2

Clean trigger resets counter to 1, and very incidence of type=1 increments counter to +1. If there are trigger and type are 0, they keep counter at last calculated value. As a main group, machine column is there, for each machine, the above calculation is executed independently.

I know to use VAR and countx related formulae to calculate running sum which filters the limited rows and then counts/sums to get the running sum, but here filtering trigger column does not make sense as I need to filter just last occurrence of trigger=1. I think, FIRSTNONBLANK kind of function be of help here, but I am unable to get it well.

Please advise.

[mahenkj2]

@OwenAugerI could use the solution offered in my actual work. Thanks a lot. In power query, it looks quite lighter than in DAX.

It would be more helpful, if you can explain that how the code is actually working. I have tough time to really understand it and so I just replicated it.

There are many resources on DAX/PowerBI, is there something for M as well?

[OwenAuger]

That's good news 🙂

Sorry, I tried to post this reply some days ago, but I was unexpectedly logged out and the post disappeared.

The key part of the query is List.Generate.

This function works a bit like a for-loop, and creates a list of items, adding one for each iteration of the loop. The arguments it takes are :

1. Initial value
2. Condition to continue loop
3. Function to derive next value from current value
4. Optional argument which I didn't use, which specifies the value to be returned based on the values produced in steps 1 & 3 (e.g. a subset of fields from a record rather than the whole record)

1. The Initial value is this, which constructs a record from the first row of SourceTable:

()=>let CurrentRow = SourceTable{[Index= 1]}, CurrentType = CurrentRow[type], CurrentMachine = CurrentRow[machine], CurrentCounter = if CurrentType = 1 then 1 else 0
in [Index = 1, machine = CurrentMachine, counter = CurrentCounter]

2. The condition to continue loop is this, which checks if the Index of the current row <= the total number of rows:

each [Index] <= RowCount,

3. The function to derive the next value from the current value (which is a record) is:

each let CurrentRow = SourceTable{[Index = [Index]+1]},
         CurrentType = CurrentRow[type], CurrentMachine = CurrentRow[machine], CurrentCleanTrigger = CurrentRow[clean trigger],
         CurrentCounter = (if CurrentMachine <> [machine] or CurrentCleanTrigger = 1 then 0 else [counter]) + CurrentType
         in [Index = [Index]+1, machine = CurrentMachine, counter = CurrentCounter]    
    )

 This is the most important part, as it contains the logic for resetting and incrementing the counter.

There are various resources out there on M.

A good article on List.Generate is here on Chris Webb's blog.

Also the Microsoft documentation is not too bad these days:

https://docs.microsoft.com/en-us/powerquery-m/m-spec-introduction

I also have a book M is for (Data) Monkey which I found quite good.

Kind regards,

Owen

[v-deddai1-msft]

Hi @mahenkj2 ,

You can also refer to the similiar post: https://stackoverflow.com/questions/55619487/is-there-a-way-in-power-query-to-calculate-a-running-to...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[OwenAuger]

Hi @mahenkj2 

Just an initial comment: this might be better handled in Power Query, using List.Generate.

However, you can write a calculated column in DAX like this:

counter =
VAR CurrentIndex = YourTable[Index]
VAR CurrentMachineStartIndex =
    CALCULATE (
        MIN ( YourTable[Index] ),
        ALLEXCEPT ( YourTable, YourTable[machine] )
    )
VAR LastCleanTriggerIndex =
    CALCULATE (
        MAX ( YourTable[Index] ),
        ALLEXCEPT ( YourTable, YourTable[machine] ),
        YourTable[clean trigger] = 1,
        YourTable[Index] <= CurrentIndex
    )
VAR LastResetIndex =
    MAX ( CurrentMachineStartIndex, LastCleanTriggerIndex )
VAR TypeCumulativeCountSinceLastReset =
    CALCULATE (
        SUM ( YourTable[type] ),
        ALLEXCEPT ( YourTable, YourTable[machine] ),
        YourTable[Index] >= LastResetIndex,
        YourTable[Index] <= CurrentIndex
    )
RETURN
    TypeCumulativeCountSinceLastReset

At least this works with your sample data and my reading of the rules, but there may well be some tweaking required if I misunderstood something.

At least this general structure should work.

Regards,

Owen

[mahenkj2]

Thanks. I am now testing it, it is crashing my Powerbi file, so now trying to work out through DAX studio. There code can execute but I have to now check the validity of the results.

I was even intending to request a Power query solution, incase that is more efficient. Calculated column have limitation that it is not so visual, second it can not be referenced later in power query in another table. If possible, can you also add a power query solution please! I would like to give a try to both.

[OwenAuger]

You're welcome! I had wondered how large the real table is, as these sorts of calculations in DAX can be rather memory-intensive.

I will look at the Power Query version using List.Generate when I have a few minutes later and get back to you. I expect it to perform better and probably be a bit more intuitive.

Kind regards,

Owen

[mahenkj2]

Hi @OwenAuger,

Thanks. Powerbi file can't work and kept processing for long. DAX studio can calculate some numbers from this formula and since I am comfortable in testing the validity of result, again tried in pbix file.

I could check that calculation of first variable is excuted well and returns the result instantly, but second variable takes infinetly long time:

counter =
VAR CurrentIndex = YourTable[Index]
VAR CurrentMachineStartIndex =
CALCULATE (
MIN ( YourTable[Index] ),
ALLEXCEPT ( YourTable, YourTable[machine] )
)
VAR LastCleanTriggerIndex =
CALCULATE (
MAX ( YourTable[Index] ),
ALLEXCEPT ( YourTable, YourTable[machine] ),
YourTable[clean trigger] = 1,
YourTable[Index] <= CurrentIndex
)

Just for testing if I put the above 2 variable and return the value of

LastCleanTriggerIndex

, it fails to complete the processing.

By the way, this table have about 100,000 rows, but expected to grow regularly.

What do you think the reason for such slowness? I would appreciate if you can suggest the power query method for this scenario. I saw another helpful answer here from @v-deddai1-msft , it contains a custom furnction, would give a try but functions are new thing for me and might take some time to understand the concept. I just do not prefer to copy and paste the code, perhaps not good habit everytime ‌
‌

[OwenAuger · Accepted solution]

Hi again @mahenkj2 

I've attached a PBIX illustrating a possible Power Query method using List.Generate, which I'm hoping refreshes in an acceptable time.

This is the code for the query called Counter that generates the counter values. This query relies on a table called YourTableSource with columns Index, clean trigger, machine, and type:

let
    // Select required columns from source table
    // Table.Buffer used but may not be necessary
    SourceTable = Table.Buffer(YourTableSource[[Index],[clean trigger],[machine],[type]]),
    RowCount = Table.RowCount(SourceTable),
    GenerateCount =
      List.Generate(
        // Function with no arguments to generate first counter record
        // Record contains [Index, machine, counter]
        ()=>let CurrentRow = SourceTable{[Index= 1]}, CurrentType = CurrentRow[type], CurrentMachine = CurrentRow[machine], CurrentCounter = if CurrentType = 1 then 1 else 0
            in [Index = 1, machine = CurrentMachine, counter = CurrentCounter],
        // Condition to continue loop
        each [Index] <= RowCount,
        // Function to produce next counter record
        each let CurrentRow = SourceTable{[Index = [Index]+1]},
         CurrentType = CurrentRow[type], CurrentMachine = CurrentRow[machine], CurrentCleanTrigger = CurrentRow[clean trigger],
         CurrentCounter = (if CurrentMachine <> [machine] or CurrentCleanTrigger = 1 then 0 else [counter]) + CurrentType
         in [Index = [Index]+1, machine = CurrentMachine, counter = CurrentCounter]    
    ),
    // Convert into table to be joined with main table
    #"Converted to Table" = Table.FromList(GenerateCount, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Index", "counter"}, {"Index", "counter"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Index", Int64.Type}, {"counter", Int64.Type}})
in
    #"Changed Type"

The output counter values appear correct but please test with your actual data.

The important parts to check are the 1st and 3rd arguments of List.Generate.

The final table is called YourTable, which joins YourTableSource and Counter.

On the original DAX version, the poor performance is essentially due to the quadratic relationship between number of sets to be included in the calculation and the number of rows, which eats up RAM. We could possibly rewrite, but I still think Power Query performance would be better.

Let me know how this goes!

Regards,

Owen

[mahenkj2]

@OwenAugerGreat help! I am giving it a try in my actual work.