Forum Discussion
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 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?
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.
9 Replies
hi KWelsh8144
can you also provide the expected column?
FreemanZ sure! i updated the original post. thanks!
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.
