[DAX] Running count of occurrence by category, reset count each period

[Anonymous]

Hi all!

I've a table with data like this

index	Series	No. of episode	Person
1	1	1	Anne
2	1	2	Mark
3	1	3	Shaun
4	1	4	Anne
5	1	5	Mark
6	2	1	Anne
7	2	2	Mark
8	2	3	Mark
9	2	4	Shaun
10	3	1	Anne
11	3	2	Anne
12	3	3	Anne

I would like to add a column counting the running occurrence of a person in a series, like this:

index	Series	No. of episode	Person	appearrance in series
1	1	1	Anne	1
2	1	2	Mark	1
3	1	3	Shaun	1
4	1	4	Anne	2
5	1	5	Mark	2
6	2	1	Anne	1
7	2	2	Mark	1
8	2	3	Mark	2
9	2	4	Shaun	1
10	3	1	Anne	1
11	3	2	Anne	2
12	3	3	Anne	3

I've created a calculated column that counts the running occurrence of a person throughout all episodes. This is my DAX for it: 

running appearance = Calculate(COUNTA(Episodes[Chaser]); FILTER(Episodes; Episodes[Chaser] = EARLIER(Episodes[Chaser]) && Episodes[Index] <= EARLIER(Episodes[Index])))

But I'm stuck on how to reset the count by each series. The series are not uniform in lenght, they can contain 10 episodes, or 170 episodes.

Many thanks if any of you can help me!

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Series]=EARLIER(Data[Series])&&Data[Person]=EARLIER(Data[Person])&&Data[No. of episode]<=EARLIER(Data[No. of episode])))

Hope this helps.

[Anonymous]

Fantastic. Thank you Ashish_Mathur!