Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello master minds ,
I have a Power BI data set with TimeStamps.
Each are 15 minutes apart.
I would like to create a column that counts the number of consecutive timestamps with the same StatecategoryName.
This is an Excel sample of my data.
The results I want to calculate are in the last column "Consecutive Identical Values".
Thanks in advance
Bo
TimeStampLocal | StateCategoryName | Index | Consecutive identical values | |
03-10-2021 | 00:00 | Downtime | 0 | 2 |
03-10-2021 | 00:15 | Downtime | 1 | 1 |
03-10-2021 | 00:30 | Uptime | 2 | 1 |
03-10-2021 | 00:45 | Downtime | 3 | 2 |
03-10-2021 | 01:00 | Downtime | 4 | 1 |
03-10-2021 | 01:15 | Uptime | 5 | 5 |
03-10-2021 | 01:30 | Uptime | 6 | 4 |
03-10-2021 | 01:45 | Uptime | 7 | 3 |
03-10-2021 | 02:00 | Uptime | 8 | 2 |
03-10-2021 | 02:15 | Uptime | 9 | 1 |
03-10-2021 | 02:30 | Downtime | 10 | 16 |
03-10-2021 | 02:45 | Downtime | 11 | 15 |
03-10-2021 | 03:00 | Downtime | 12 | 14 |
03-10-2021 | 03:15 | Downtime | 13 | 13 |
03-10-2021 | 03:30 | Downtime | 14 | 12 |
03-10-2021 | 03:45 | Downtime | 15 | 11 |
03-10-2021 | 04:00 | Downtime | 16 | 10 |
03-10-2021 | 04:15 | Downtime | 17 | 9 |
03-10-2021 | 04:30 | Downtime | 18 | 8 |
03-10-2021 | 04:45 | Downtime | 19 | 7 |
03-10-2021 | 05:00 | Downtime | 20 | 6 |
03-10-2021 | 05:15 | Downtime | 21 | 5 |
03-10-2021 | 05:30 | Downtime | 22 | 4 |
03-10-2021 | 05:45 | Downtime | 23 | 3 |
03-10-2021 | 06:00 | Downtime | 24 | 2 |
03-10-2021 | 06:15 | Downtime | 25 | 1 |
Solved! Go to Solution.
Hi @BST ,
Based on your description, you can create some columns as follows.
Mark =
var x1=MAXX(FILTER('Consecutive',[Index]=EARLIER([Index])-1),[StateCategoryName])
var x2=IF([StateCategoryName]=x1,0,1)
return
x2
Category = SUMX(FILTER(ALL('Consecutive'),[Index]<=EARLIER('Consecutive'[Index])),[Mark])
Consecutive_Test = RANKX(FILTER(ALL('Consecutive'),[Category]=EARLIER(Consecutive[Category])),[Index],,DESC)
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @BST ,
Based on your description, you can create some columns as follows.
Mark =
var x1=MAXX(FILTER('Consecutive',[Index]=EARLIER([Index])-1),[StateCategoryName])
var x2=IF([StateCategoryName]=x1,0,1)
return
x2
Category = SUMX(FILTER(ALL('Consecutive'),[Index]<=EARLIER('Consecutive'[Index])),[Mark])
Consecutive_Test = RANKX(FILTER(ALL('Consecutive'),[Category]=EARLIER(Consecutive[Category])),[Index],,DESC)
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thank you very much - just what I was looking for!
It is very nice and very close to what I am looking for, but I have trouble nailing the last bit.
I replaced the 'Table1[Date] with my 'index'-columm as date-level is too coarse.
The result is the accumulative sum for the period in question.
It does not reset each time the statecategory changes.
Probably I need to add another condition but am not sure how and which.
Here is example with the results from the new column:
@BST ,Try to check this solution, if that can help
https://community.powerbi.com/t5/Desktop/Count-last-actual-consecutive-sorted-rows/m-p/151191#M65364
Thanks - I'll test it later today and let you know if it worked.
Bo
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |