Running count of occurrences, with filters

[swisdom]

I have a long set of data consisting of unit serial numbers, service dates, and Service Numbers.

I want to create a running count of the number of times each Serial Number has been returned, ordered by occurance.

To complicate things further, there are multiple "types" in the data - a serial number may appear in more than one type, but I only want to count if Type = "Serv" (if it helps, in instances where the Type is anything other than Serv, there will be no SVO number). Also, frequently multiple lines will be created for the same return, indicated by an alpha character at the end of the SVO number. In these cases, I do not want it to count as a separate occurance.

I tried creating an Index column to help, but I'm not sure if it is actually useful.

An example of my data, and what I would like to create in the last (red) column:

Type	Serial	SVO	Date	Occurance
Serv	123456	83812	6/9/2022	1
Test	123456		3/30/2022	NA
Serv	456789	74659	2/4/2020	1
Serv	789129	76598	6/12/2021	1
Serv	123456	84749	10/30/2022	2
Serv	567890	84505	1/19/2022	1
Serv	567890	84505A	1/20/2022	NA
Serv	123456	84596	1/3/2023	3
Serv	567890	86954	1/30/2022	2

What I've got so far is just a flat count, which seems to work OK except it doesnt do the exclusions, and it doesnt number them by occurance

Multi Count = 
    COUNTX (
    FILTER ( 'Data', EARLIER ( 'Data'[SERIAL] ) = 'Data'[SERIAL]), 'Data'[SERIAL] )

This gives something like this:

Type	Serial	SVO	Date	Occurance
Serv	123456	83812	6/9/2022	4
Test	123456		3/30/2022	4
Serv	456789	74659	2/4/2020	1
Serv	789129	76598	6/12/2021	1
Serv	123456	84749	10/30/2022	4
Serv	567890	84505	1/19/2022	3
Serv	567890	84505A	1/20/2022	3
Serv	123456	84596	1/3/2023	4
Serv	567890	86954	1/30/2022	3

I've also tried this, but it seems to just give.. I'm not sure, some sort of nonsense that isn't what I'm looking for:

Multi Occurance = 
calculate( countrows('Data'), 
filter('Data', 'Data'[TYPE] = "Serv" &&
'Data'[SERIAL] = earlier('Data'[SERIAL]) &&
not( left('Data'[SVO],5) = left(earlier('Data'[SVO]),5) )))

I appreciate any help!

[bolfri · Accepted solution]

NA_logic = 
SWITCH(TRUE(),
    'Sample'[Type]<>"Serv","NA",
    NOT(RIGHT([SVO],1) in {"0","1","2","3","4","5","6","7","8","9"}), "NA",
    "OK"
    )

It's better to split this into two columns. 🙂

Multi Count = 
var serial = 'Sample'[Serial]
var current_date = 'Sample'[Date]
var multicount =
    COUNTROWS(
        FILTER('Sample',
        'Sample'[NA_logic] <> "NA"
        && 'Sample'[Serial]=serial
        && 'Sample'[Date] <= current_date
        )
    )
return IF('Sample'[NA_logic] <> "NA",multicount,0)

 Result:

[swisdom]

That worked perfectly, thank you!

[bsheffer]

sounds like you could do this with a simple calculate(countrows('data'), with a filter clause to remove anything you don't want.