Forum Discussion
Solved
IF statement with multiple conditions for unique IDs
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 woul...
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.
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.
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?
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.
THank you!
You are welcome.