cancel
Showing results for
Did you mean:
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

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

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.

2 REPLIES 2
Super User

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

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.

Frequent Visitor

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

Fantastic. Thank you Ashish_Mathur!