Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I know you are probably thinking oh dear here we go again not another one...
And for this I apologise however I cannot seem to get my head around how to achieve this and looking for your help!
I have a table that looks like this:
I am trying to achieve the results shown in the 5th column with formula (below) that is seen in the 7th column.
It all works great until row 7 when it starts to go wrong...
The formula i am using is:
CountConsecutiveValues (Each Category) = VAR LastTableDate = 'Table'[Date] VAR CumulativeValues = 'Table'[SumCumulativeValues (Each Category)] RETURN MAX( COUNTX ( CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Category] ), 'Table'[Date] <= LastTableDate, 'Table'[SumCumulativeValues (Each Category)] < CumulativeValues ), 'Table'[Values] ), IF('Table'[Values]>0,1,0) )
Any suggestions where i am going wrong?
I'm guessing that you need to take a look at the EARLIER function. So, basically what the pseudo-code should look like would be something along the lines of:
Column =
VAR __tmpTable = FILTER(ALL('Table'),[Id]<EARLIER([Id])) //return all rows before current row
VAR __maxBlank = MAXX(FILTER(__tmpTable,ISBLANK([Values]),[Id]) //return the highest index where Values is blank
RETURN COUNTROWS(FILTER(__tmpTable,[Id]>__maxBlank)) //returns count of rows from the temp table that are past the highest blank row Id
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |