Forum Discussion
Anonymous
6 years agoNot applicable
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] )+0File is attached after signature
2 Replies
- amitchandakSuper User
Anonymous
Please try a new column like
What you want = COUNTX(FILTER(data,[Country]=EARLIER([Country]) && [date]<=EARLIER([date]) && [Test]>0),[date] )+0File is attached after signature
- AnonymousNot applicable
Thanks , works perfect.