How to reset a measure counter depending on other column data

[Anonymous]

Hi all,

I've trying to automate the calculation showed below in the last column "Batch Number of Campaign" and no way can I get it right.

The counter starts from 1 in the first batch and continue incrementing by one when "Batch changes" and resets when there is a "CF" in product.

Can anybody give me some advise please? Thanks a lot!

Product	Batch	Shift	Date	Batch Number of Campaign
Product A	7H2S	2.M	07-01-2020	1
Product A	7H2S	3.T	07-01-2020	1
Product A	7H2S	1.N	08-01-2020	1
Product A	7H3L	2.M	08-01-2020	2
Product A	7H3L	3.T	08-01-2020	2
Product A	7H3L	1.N	09-01-2020	2
Product A	7H3P	2.M	09-01-2020	3
Product A	7H3P	3.T	09-01-2020	3
CF4	7H3P	3.T	09-01-2020	0
Product B	848E	1.N	10-01-2020	1
Product B	848E	2.M	10-01-2020	1
Product B	848E	2.M	13-01-2020	1
Product B	848E	3.T	13-01-2020	1
Product B	848F	1.N	14-01-2020	2
Product B	848F	2.M	14-01-2020	2
Product B	848F	3.T	14-01-2020	2
Product B	848F	1.N	15-01-2020	2
Product B	848F	2.M	15-01-2020	2
CF2	848F	2.M	15-01-2020	0
CF2	848F	3.T	15-01-2020	0
Product C	9P9X	3.T	15-01-2020	1
Product C	9P9X	1.N	16-01-2020	1
Product C	9P9X	2.M	16-01-2020	1
Product C	9P9X	3.T	16-01-2020	1
Product C	9P9X	1.N	17-01-2020	1
Product C	9P9X	2.M	17-01-2020	1
Product C	9P9X	3.T	17-01-2020	1

[Anonymous · Accepted solution]

Hi  @Anonymous,

Create a calculated column as below:

Batch Number of Campaign = 
var _CFcheck=SEARCH("CF",'Table'[Product],1,BLANK())
Return
IF(_CFcheck=1,0,RANKX(FILTER('Table','Table'[Product]=EARLIER('Table'[Product])),'Table'[Batch],,ASC,Dense))

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , create a sub-category type of rank on Date or Shift , that should work

refer For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

Check for Category Rank and Sub Category Rank