Forum Discussion
Lookup Multiple Rows and Return Yes or No
Hi all,
I have a table which looks like this:
| container_nr | Status |
| OOLU3874481 | completed |
| OOLU3874481 | pending |
| OOLU3874481 | rejected |
| OOLU3333333 | pending |
I am looking for a formula for the following. If there are multiple rows with the same 'container_nr', and the 'Status' of one of these rows is 'completed', I want a 'yes' in an extra column:
Hope someone can help me with this?!
regards,
John
| container_nr | Status | completed? |
| OOLU3874481 | completed | yes |
| OOLU3874481 | pending | yes |
| OOLU3874481 | rejected | yes |
| OOLU3333333 | pending | 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.
7 Replies
You want EARLIER:
completed = VAR __table = FILTER(ALL('Table14'),[container_nr] = EARLIER([container_nr]) && [Status] = "completed") RETURN IF(COUNTX(__table,[Status])>=1,"yes","no")See table 14 of attached.
Also, here is a good article on EARLIER:
See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
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.
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.
You are welcome.