Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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)
@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
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
@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)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |