cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nynni Frequent Visitor
Frequent Visitor

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

Hi all!

 

I've a table with data like this

 

indexSeriesNo. of episodePerson
111Anne
212Mark
313Shaun
414Anne
515Mark
621Anne
722Mark
823Mark
924Shaun
1031Anne
1132Anne
1233Anne


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

 

indexSeriesNo. of episodePerson
appearrance in series
111Anne1
212Mark1
313Shaun1
414Anne2
515Mark2
621Anne1
722Mark1
823Mark2
924Shaun1
1031Anne1
1132Anne2
1233Anne3

 

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
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.

 

Untitled.png

2 REPLIES 2
Super User
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.

 

Untitled.png

nynni Frequent Visitor
Frequent Visitor

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

Fantastic. Thank you Ashish_Mathur!