How to count colums with consecutive value

[Danielecc]

Hi Everyone,

I need to know how to count columns with consecutive value where the value is "CC", but I need the MAX, form example:

I need to count only in the columns NLxx and with values "CC" (find the max of consecutive).

I colored in red the cases that make the match form me and the result expected is a calculated column like the last red column in the image.

Thanks a lot for your help.

CS01	202101_D	NL01	CS02	202102_D	NL02	CS03	202103_D	NL03	CS04	202104_D	NL04	CS05	202105_D	NL05	CS06	202106_D	NL06	CS07	202107_D	NL07	CS08	202108_D	NL08	CS09	202109_D	NL09	CS10	202110_D	NL10	CS11	202111_D	NL11	CS12	202112_D	NL12	Expected
0	25-01-2021	CC	0	26-02-2021	CC	0	25-03-2021	CC	0	01-04-2000	CC	0	01-05-2000	LN	0	01-06-2000	LN	0	07-07-2021	LN	8	09-08-2021	CC	18	08-09-2021	CC	22	08-10-2021	CC	27	09-11-2021	CC	32	09-12-2021	CC	5
1	25-01-2021	LN	2	26-02-2021	CC	4	25-03-2021	LN	8	05-04-2021	CC	10	10-05-2021	CC	20	08-06-2021	CC	28	08-07-2021	CC	35	09-08-2021	CC	45	08-09-2021	CC	55	08-10-2021	CC	63	09-11-2021	LN	70	09-12-2021	LN	7
4	25-01-2021	CC	9	26-02-2021	CC	18	25-03-2021	CC	27	05-04-2021	CC	35	10-05-2021	CC	62	08-06-2021	CC	77	08-07-2021	CC	103	09-08-2021	LN	124	08-09-2021	LN	143	08-10-2021	CC	160	09-11-2021	CC	180	09-12-2021	LN	7

Best regards...

[Anonymous]

Hi @Danielecc ,

It can only achieve ordinary counting, instead of  consecutive value.

Please have atry.

Create a column.

Column = 
var NL01_= IF('Table'[NL01]="CC",1,0)
var NL02_= IF('Table'[NL02]="CC",1,0)
var NL03_= IF('Table'[NL03]="CC",1,0)
var NL04_= IF('Table'[NL04]="CC",1,0)
var NL05_= IF('Table'[NL05]="CC",1,0)
var NL06_= IF('Table'[NL06]="CC",1,0)
var NL07_= IF('Table'[NL07]="CC",1,0)
var NL08_= IF('Table'[NL08]="CC",1,0)
var NL09_= IF('Table'[NL09]="CC",1,0)
var NL10_= IF('Table'[NL10]="CC",1,0)
var NL11_= IF('Table'[NL11]="CC",1,0)
var NL12_= IF('Table'[NL12]="CC",1,0)
var result=NL01_*1+NL02_*1+NL03_*1+NL04_*1+NL05_*1+NL06_*1+NL07_*1+NL08_*1+NL09_*1+NL10_*1+NL11_*1+NL12_*1
return result

DAX is for calculated columns, not calculated rows. I tried to pivot on your data, but couldn't get the look you wanted.

Please vote for the function.

https://ideas.powerbi.com/ideas/idea/?ideaid=01763e26-eb76-48df-9006-dfa3ca08d572

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.