cancel
Showing results for
Did you mean:
Helper II

## Count Consecutive Rows by multiple Column Filters

Looking to Calculate the Consecutive Weeks (Rows) at 'Combined Color' filtered by 'Project ID'

Ideally, it would be a Column that would count how many weeks (Rows) it was at the same 'Combined Color'

Expected Result Project ID 344 , Index 4, Combined Color Orange, Count 2

PBi File for Troubleshooting issues online.pbix

I have figured out a way to calculate consecutive rows, unfortunately, that is as far as I have got thus far.

Combined Color repeats =
VAR _Prev = CALCULATE(MAX(IssuesArchived[Combined Color #]), FILTER(IssuesArchivedAND(AND(EARLIER(IssuesArchived[Index]) -1 = IssuesArchived[Index]IssuesArchived[Program] = EARLIER(IssuesArchived[Program])), IssuesArchived[Project ID] = EARLIER(IssuesArchived[Project ID]))))

RETURN
If(_Prev <> IssuesArchived[Combined Color #],0,1)
1 ACCEPTED SOLUTION
Helper II

Solved by this Calculated Column:

Column (Aldert) =

var rowPickup = CALCULATE(MAX(IssuesArchived[Index]),
FILTER('IssuesArchived',
IssuesArchived[Project ID] = EARLIER(IssuesArchived[Project ID])
&& IssuesArchived[Index] < EARLIER(IssuesArchived[Index])
&& IssuesArchived[Combined Color] <> EARLIER(IssuesArchived[Combined Color])))
return
IF(rowPickup = BLANK(),
COUNTROWS(FILTER(IssuesArchived,
IssuesArchived[Project ID] = EARLIER(IssuesArchived[Project ID])
&& IssuesArchived[Index] < EARLIER(IssuesArchived[Index]))) + 1 ,
IssuesArchived[Index] - rowPickup)

Helper II

Solved by this Calculated Column:

Column (Aldert) =

var rowPickup = CALCULATE(MAX(IssuesArchived[Index]),
FILTER('IssuesArchived',
IssuesArchived[Project ID] = EARLIER(IssuesArchived[Project ID])
&& IssuesArchived[Index] < EARLIER(IssuesArchived[Index])
&& IssuesArchived[Combined Color] <> EARLIER(IssuesArchived[Combined Color])))
return
IF(rowPickup = BLANK(),
COUNTROWS(FILTER(IssuesArchived,
IssuesArchived[Project ID] = EARLIER(IssuesArchived[Project ID])
&& IssuesArchived[Index] < EARLIER(IssuesArchived[Index]))) + 1 ,
IssuesArchived[Index] - rowPickup)