Changes count

[slyfox]

Hi there,

Have a table with product to produce information

basing on columns 'Start Prod Date', 'Product to Produce' and 'Type' I need to create columns 'Changes' and 'Number of Times' 

Start Prod Date	ProductToProduce	Type	Changes	Number Of Times
01-02-18	Chocolate A	A
01-02-18	Chocolate A	A	AB	1
01-02-18	Chocolate B	B
01-02-18	Chocolate B	B	BA	1
05-02-18	Chocolate A	A	AC	1
05-02-18	Chocolate C	C
05-02-18	Chocolate C	C
05-02-18	Chocolate C	C
09-02-18	Chocolate C	C	CB	1
09-02-18	Chocolate B	B	BA	1
09-02-18	Chocolate A	A
09-02-18	Chocolate A	A

[Anonymous · Accepted solution]

@slyfox,

Add an index column in your table in Query Editor of Power BI Desktop.

Then create the following columns in your table.

Column = CALCULATE(FIRSTNONBLANK(Table[Type],1),FILTER(Table,Table[Index]=EARLIER(Table[Index])+1))

Changes = IF(Table[Type]=Table[Column] || ISBLANK(Table[Column]),BLANK(),CONCATENATE(Table[Type],Table[Column]))

Number Of times = CALCULATE(COUNTA(Table[Changes]),ALLSELECTED(Table[Index]))

Regards,
Lydia