IF statement with multiple conditions for unique IDs

[KWelsh8144]

I'm trying to get an if statement that looks at each unique ID and then meets the following criteria: 

Each ID must have Complete = yes, and Type = 20 OR Type = 13 AND 23

The flag column would be the expected result.

ID	Type	Complete	Flag
123	20	Yes	1
123	4	Yes	1
456	20	No	0
456	18	Yes	0
456	5	Yes	0
789	23	Yes	1
789	13	Yes	1
789	6	Yes	1

I tried something like this: 

*Flag = if(
and(
or(
and(Table[Type] = 23, Table[Type] = 13),
Table[Type] = 20),
Table[Complete]="Yes"),
1, 0) 

But this doesn't seem to work since each ID has multiple rows. Any ideas? 

[Ashish_Mathur]

Hi,

Write this calculated column formula

Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Complete]="Yes"&&(Data[Type]=20)||Data[ID]=EARLIER(Data[ID])&&Data[Complete]="Yes"&&(Data[Type]=23||Data[Type]=13)))>=1,1,0)

Hope this helps.

[KWelsh8144]

Thanks for the response - but this isn't working for me either.  If someone has a 13 or 23, it's flagging that as a 1, when they need to have both 13 and 23 , or just a 20 in the type. Any ideas?

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula instead

Column1 = 1*(or(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Complete]="Yes"&&(Data[Type]=20))),and(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Complete]="Yes"&&Data[Type]=23)),CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Complete]="Yes"&&Data[Type]=13)))))

Hope this helps.

[KWelsh8144]

THank you!

[Ashish_Mathur]

You are welcome.

[FreemanZ]

hi @KWelsh8144 

can you also provide the expected column?

[KWelsh8144]

@FreemanZ sure! i updated the original post. thanks!

[FreemanZ]

hi @KWelsh8144 

could you explain how comes 1 for 4 and 6?

[KWelsh8144]

I don't necessary need it that way if it could only show for the conditions that it meets - that's fine as well, but what i was getting at was that, that particular unique ID meets the criteria of the if statement. However, if it would instead only show a 1 for records with a 20 OR 23 and 13, that would work too.