Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
Fantastic. Thank you Ashish_Mathur!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |