Forum Discussion
Lookup Multiple Rows and Return Yes or No
Hi,
Try this calculated column formula
=IF(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[container_nr]=EARLIER(Data[container_nr])&&Data[Status]="Completed"))>=1,"Yes","No")
Hope this helps.
Are there multiple tables involved here? The example I put together was for everything in a single table and it seemed like it was returning the correct status.
good day Greg,
here is what i did: I took a beer, had a good rest, tried it again and found out it is woking fine.
Thank you so much!
john
Hi Greg,
I am almost there, but one thing is not correct.
your formula gives me also a 'yes' for the OOLU3890311.
This is not correct, as this container_nr has the status PENDING.
Can you please advise?
completed? =
VAR __table =
FILTER (
ALL ( OOCL_RTM_Depotsmart_PTI );
OOCL_RTM_Depotsmart_PTI[Container_nr] = EARLIER ( [container_nr] )
&& [Status] = "completed"
)
RETURN
IF ( COUNTX ( OOCL_RTM_Depotsmart_PTI; [Status] ) >= 1; "yes"; "no" )
| container_nr | Container_nr | Status | completed? |
| OOLU3874481 | OOLU3874481 | completed | yes |
| OOLU3874481 | OOLU3874481 | pending | yes |
| OOLU3874481 | OOLU3874481 | rejected | yes |
| OOLU3890311 | OOLU3890311 | pending | yes |
Are there multiple tables involved here? The example I put together was for everything in a single table and it seemed like it was returning the correct status.
good day Greg,
here is what i did: I took a beer, had a good rest, tried it again and found out it is woking fine.
Thank you so much!
john