Lookup Multiple Rows and Return Yes or No

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

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.

[Greg_Deckler · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

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.

[Anonymous]

good day  Ashish,

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

[Ashish_Mathur]

You are welcome.

[Greg_Deckler]

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/3395...

[Anonymous]

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

[Greg_Deckler · Accepted solution]

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.

[Anonymous · Accepted solution]

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