Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jwi1
Post Patron
Post Patron

Lookup Multiple Rows and Return Yes or No

Hi all,

 

I have a table which looks like this:

container_nrStatus
OOLU3874481completed
OOLU3874481pending
OOLU3874481rejected
OOLU3333333pending

 

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_nrStatuscompleted?
OOLU3874481completedyes
OOLU3874481pendingyes
OOLU3874481rejectedyes
OOLU3333333pendingno
3 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

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

 

 

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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_nrContainer_nrStatuscompleted?
OOLU3874481OOLU3874481completedyes
OOLU3874481OOLU3874481pendingyes
OOLU3874481OOLU3874481rejectedyes
OOLU3890311OOLU3890311pendingyes

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.