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

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

thank you very much - just what I was looking for!