Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

auto increment column base on condition

Hi all,

 

I need to create a new column (Dax or PowerQuery) based on other column. This is my scenario

 

 

Country	date	Test	Indice
China	22/01/2020 0:00:00	548	2179
China	23/01/2020 0:00:00	643	2180
China	24/01/2020 0:00:00	920	2181
Spain	22/01/2020 0:00:00	0	9769
Spain	23/01/2020 0:00:00	0	9770
Spain	24/01/2020 0:00:00	0	9771
US	22/01/2020 0:00:00	1	11089
US	23/01/2020 0:00:00	1	11090
US	24/01/2020 0:00:00	2	11091

 

 

And this is what i would like to obatin:

 

 

Country	date	Test	Indice	WhatIWantIs
China	22/01/2020 0:00:00	548	2179	1
China	23/01/2020 0:00:00	643	2180	2
China	24/01/2020 0:00:00	920	2181	3
Spain	22/01/2020 0:00:00	0	9769	0
Spain	23/01/2020 0:00:00	0	9770	0
Spain	24/01/2020 0:00:00	1	9771	1
US	22/01/2020 0:00:00	1	11089	1
US	23/01/2020 0:00:00	1	11090	2
US	24/01/2020 0:00:00	2	11091	3

 

 

If column test is 0 , new column have to be 0 but if test is > 0 new column have to increment +1 also taking account the columns country and date to guarantee the integrity of data.

 

Thanks 

  • Anonymous 

    Please try a new column like

     

    What you want = COUNTX(FILTER(data,[Country]=EARLIER([Country]) && [date]<=EARLIER([date]) && [Test]>0),[date] )+0

     

     

    File is attached after signature

     

2 Replies

  • Anonymous 

    Please try a new column like

     

    What you want = COUNTX(FILTER(data,[Country]=EARLIER([Country]) && [date]<=EARLIER([date]) && [Test]>0),[date] )+0

     

     

    File is attached after signature

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks , works perfect.