Forum Discussion
Check previous values on same ID
Hey there!
I've got a question regarding the following:
In my dataset I have the following columns
Customer ID | Product | Up-to-date |
1 TV 1
1 Fridge 0
2 TV 1
3 Car 0
Up-to-date in this regard means if the information on the product, assigned to the customer, is complete (Filled in correctly)
In my example you can see that customer 1 has 2 products under its name. 1 being up-to-date, the other one isn't.
Question: Is it possible to create a new column where if Customer has 1 or more values of 0 in Column "up-to-date", return value 0 for all rows (on that specific customer) (See example below)
Customer ID | Product | Up-to-date | IndexUpToDate
1 TV 1 0
1 Fridge 0 0
2 TV 1 1
3 Car 0 0
I would love to hear from you. If you have any questions, please do ask.
Kind regards and thanks in advance,
Daniël
4 Replies
Anonymous , Try a new column like
IndexUpToDate = if(countx(filter(table[Customer ID] = earlier([Customer ID]) && [Up-to-date]=0),[Up-to-date])+0>0,0,[Up-to-date])
or
IndexUpToDate = if(countx(filter(table[Customer ID] = earlier([Customer ID]) && [Up-to-date]=0),[Up-to-date])+0>0,0,1)
Thanks for the swift reply!
When I try to use "if(countx(filter(table[Customer ID] = earlier([Customer ID]) && [Up-to-date]=0),[Up-to-date])+0>0,0,[Up-to-date])"
It doesn't let me select the first table[Customer ID], it wants a Table, not a column name.
Did I need to do anything beforehand, to make the column formula work?
Anonymous
you can try this
Column = VAR _count=CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[customerID]=EARLIER('Table'[customerID])&&'Table'[up-to-date]=0)) return if(_count>0,0,'Table'[up-to-date])
