Check previous values on same ID

[Anonymous]

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

[amitchandak]

@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)

[Anonymous]

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?

[Greg_Deckler]

@Anonymous - I believe that should be:

if(
  countx(
    filter(
      table,
      [Customer ID] = earlier([Customer ID]) && 
        [Up-to-date]=0
    ),
    [Up-to-date]
  )+0>0,
  0,
  [Up-to-date]
)

I think you missed a comma after table

[ryan_mayu]

@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])