Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there!
I am looking to make a counter that counts the consecutive rows and resets when there is a nonconsecutive value.
Here is an example:
Animal | Counter |
Tiger | 1 |
Tiger | 2 |
Tiger | 3 |
Tiger | 4 |
Tiger | 5 |
Lion | 1 |
Lion | 2 |
Lion | 3 |
Tiger | 1 |
Tiger | 2 |
Elephant | 1 |
Elephant | 2 |
Elephant | 3 |
Tiger | 1 |
Tiger | 2 |
Tiger | 3 |
Any thoughts?
Thanks!
Solved! Go to Solution.
See if this works:
Column 2 = VAR __index = CALCULATE(MAX([Index])) VAR __tmpTable1 = FILTER('Table34',[Animal]=EARLIER([Animal])&&[Index]<EARLIER([Index])) VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__diff",[Index] - MAXX(FILTER(ALL('Table34'),[Index]<EARLIER([Index]) && [Animal]=EARLIER([Animal])),[Index])) VAR __max = MAXX(__tmpTable2,[Index]) VAR __maxStart = MAXX(FILTER(__tmpTable2,[__diff]>1),[Index]) VAR __tmpTable3 = FILTER(__tmpTable2,[Index]>=__maxStart) RETURN IF(ISBLANK(__max),1,IF(__max=[Index]-1,COUNTX(__tmpTable3,[Index])+1,1))
PBIX is attached.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |