Help on making a new column with a condition

[aszpic]

Hello everyone,

I couldn't find a way to do this:

I have a table with 2 columns. The first column are some codes, which CAN be repeated. The 2nd column is a binary unmber (0/1).

Example:

CODES   NUMBER

codeA   1

codeA   1

codeA   0

codeA   0

codeB   1

codeC   0

codeC   0

I need to make a 3rd column, with this logic: "If (at least one of the NUMBERs of a code is 1, then1, else 0)". So in the 3rd column, all the registers of codeA would be 1 because there are 2 of the NUMBERs of codeA that is 1. So the result would be:

CODES   NUMBER  3rdColumn

codeA   1   1

codeA   1   1

codeA   0   1

codeA   0   1

codeB   1   1

codeC   0   0

codeC   0   0

Thanks in advance!

[tringuyenminh92 · Accepted solution]

Hi @aszpic,

Firstly, I will set Number column to text to prevent it sum/count that column in table/matrix

Create Calculated column:

3rd = IF(CALCULATE(COUNT(Sheet1[Number]),filter(all(Sheet1),Sheet1[Number]="1" && Sheet1[CODES ]=EARLIER(Sheet1[CODES ]) ))>0,"1","0")

Earlier method to get current row, All method to select and compare in all rows in table

[aszpic]

thanks a lot! It threw me an error because it can't compare numbers with text values, but with a little variation it worked perfectly.