Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

counting consecutive identical values - calculated column

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 Stateca...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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: