Creating index bins from repeated instances

[subhashg]

Hi,

Looking for a DAX folmula to create below output.

I have sensor status column which changes between 0 & 1s. Need to create a bins (assending numbers) for each time the status turns to 1. I have index column in addition.

Besed on 2nd column I need 3rd column as resultant output. Please help

Index    SensorStatus    Bins(Output)

1           0                      0

2           0                      0

3           1                      1

4           1                      1

5           0                      0

6           1                      2

7           1                      2

8           1                      2

9           0                      0

10         0                      0

11         1                      3

12         0                      0

[amitchandak]

@subhashg , This is a Continuous streak ,

Streak
https://community.powerbi.com/t5/Desktop/Build-measure-or-column-to-show-a-streak-by-consecutive-dat...
https://community.powerbi.com/t5/Desktop/Win-Losing-Streak/td-p/273547
https://community.powerbi.com/t5/Desktop/Count-last-actual-consecutive-sorted-rows/m-p/151191#M65364

I done for dates. see if that can help

Continuous streak : https://youtu.be/GdMcwvdwr0o

[subhashg]

I couldnt see what i really needed in your above post. Please help.

I actually need to increament the Bins everytime SensorStatus turns from 0 to 1 & keep that incremented number untill status turns back to 0.

Status   Bin

0           0

1           1

1           1

0           0

1           2

1           2

1           2

0           0

0           0

1           3     and so on

[amitchandak · Accepted solution]

@subhashg , Try new columns

Two new column 

Change = var _1 = minx(filter(Data, [Index] = EARLIER([Index]) -1) ,[SensorStatus]) 
return if([SensorStatus] =1 && _1=0 , 1, 0)

Bin = if([SensorStatus]=1, sumx(FILTER(Data, [Index] <=EARLIER(Data[Index])),[Change]),0)