How to get value from another table based on multiple criteria

[Anonymous]

Hello,

I'm in stuck with my problem. Let's say I have 2 tables: Quotations and Shipments:

Quotations table:

Branch	Department	QuoteID	Local Client	SrvLevel	Origin	Destination	Start Date	Expiry Date
BR2	241	QMT200002851	Customer1	LIF	NLRTM	DEBRV	2018.06.02	2018.06.30
BR2	241	QMT200002852	Customer2	FIL	NLRTM	DEBRV	2018.06.02	2018.06.15
BR2	241	QMT200002853	Customer4	LIL	KRPUS	DEBRV	2018.06.02	2018.06.30
BR2	241	QMT200002854	Customer5	LID	KRPUS	DEBRV	2018.06.02	2018.06.30
BR2	241	QMT200002855	Customer7	LIF	DEHAM	DEBRV	2018.06.02	2018.06.30
BR2	241	QMT200002856	Customer8	LIF	DEHAM	DEBRV	2018.06.02	2018.06.30
BR2	241	QMT200002857	Customer9	LID	CNSHA	DEBRV	2018.06.03	2018.06.15
BR2	241	QMT200002858	Customer10	LIF	DEHAM	DEBRV	2018.06.01	2018.06.30

Shipments table:

Branch	Department	Shipment ID	Local Client	Srv Level	Origin	Destination	Departure	QuoteID
BR2	241	SHP2800120	Customer1	LIL	NLRTM	DEBRV	2018.05.29
BR2	241	SHP2800129	Customer10	LIF	DEHAM	DEBRV	2018.06.06
BR2	241	SHP2800130	Customer10	LIF	DEHAM	DEBRV	2018.06.24
BR2	241	SHP2800121	Customer2	LIF	NLRTM	DEBRV	2018.06.10
BR2	241	SHP2800122	Customer3	LIF	DEHAM	DEBRV	2018.06.10
BR2	241	SHP2800123	Customer4	LIL	KRPUS	DEBRV	2018.04.30
BR2	241	SHP2800124	Customer5	LIF	KRPUS	DEBRV	2018.06.16
BR2	241	SHP2800125	Customer6	LIF	CNSHA	DEBRV	2018.06.16
BR2	241	SHP2800126	Customer7	LIF	DEHAM	DEBRV	2018.06.16
BR2	241	SHP2800127	Customer8	LIF	DEHAM	DEBRV	2018.06.03
BR2	241	SHP2800131	Customer8	LIF	DEHAM	DEBRV	2018.06.20
BR2	241	SHP2800128	Customer9	LIL	CNSHA	DEBRV	2018.06.15

My goal is to transfer QuoteID from Quotations table into Shipments table if such conditions are met:

Branch=Branch

Department=Department

LocalClient=LocalClient

SrvLevel=SrvLevel

Origin=Origin

Destination=Destination

Departure is between StartDate and ExpiryDate

My expected result in Shipments table should look like this:

Branch	Department	Shipment ID	Local Client	Service Level	Origin	Destination	Departure	QuoteID
BR2	241	SHP2800120	Customer1	LIL	NLRTM	DEBRV	2018.05.29
BR2	241	SHP2800129	Customer10	LIF	DEHAM	DEBRV	2018.06.06	QMT200002858
BR2	241	SHP2800130	Customer10	LIF	DEHAM	DEBRV	2018.06.24	QMT200002858
BR2	241	SHP2800121	Customer2	LIF	NLRTM	DEBRV	2018.06.10
BR2	241	SHP2800122	Customer3	LIF	DEHAM	DEBRV	2018.06.10
BR2	241	SHP2800123	Customer4	LIL	KRPUS	DEBRV	2018.04.30	QMT200002853
BR2	241	SHP2800124	Customer5	LIF	KRPUS	DEBRV	2018.06.16
BR2	241	SHP2800125	Customer6	LIF	CNSHA	DEBRV	2018.06.16
BR2	241	SHP2800126	Customer7	LIF	DEHAM	DEBRV	2018.06.16	QMT200002855
BR2	241	SHP2800127	Customer8	LIF	DEHAM	DEBRV	2018.06.03	QMT200002856
BR2	241	SHP2800131	Customer8	LIF	DEHAM	DEBRV	2018.06.20	QMT200002856
BR2	241	SHP2800128	Customer9	LIL	CNSHA	DEBRV	2018.06.15

Thanks in advance!

Tomas

[Anonymous]

Hello

I created following columns 

In Quotations table -

1. Concat = Quotations[Branch] & Quotations[Department] & Quotations[Local Client] & Quotations[SrvLevel] & Quotations[Origin] & Quotations[Destination]

In Shipments table -

1. Concat = Shipments[Branch] & Shipments[Department] & Shipments[Local Client] & Shipments[Srv Level] & Shipments[Origin] & Shipments[Destination]

2. QuoteID =
IF(ISBLANK(LOOKUPVALUE(Quotations[Branch],Quotations[Concat],Shipments[Concat])),"",
CALCULATE(VALUES(Quotations[QuoteID]),
FILTER(Quotations,Shipments[Departure]>Quotations[Start Date] && Shipments[Departure] < Quotations[Expiry Date]),FILTER(Quotations,Quotations[Concat]=Shipments[Concat]) )
)

You might have to play with QuoteID field a little to replace "greater than" or "less than" with appropriate "greater than or equal to" or "less than or equal to"

It gives me correct results otherwise -

QuoteID	Shipment ID	Origin	Local Client	Destination	Branch
	SHP2800123	KRPUS	Customer4	DEBRV	BR2
	SHP2800120	NLRTM	Customer1	DEBRV	BR2
	SHP2800121	NLRTM	Customer2	DEBRV	BR2
	SHP2800122	DEHAM	Customer3	DEBRV	BR2
	SHP2800124	KRPUS	Customer5	DEBRV	BR2
	SHP2800125	CNSHA	Customer6	DEBRV	BR2
	SHP2800128	CNSHA	Customer9	DEBRV	BR2
QMT200002855	SHP2800126	DEHAM	Customer7	DEBRV	BR2
QMT200002856	SHP2800127	DEHAM	Customer8	DEBRV	BR2
QMT200002856	SHP2800131	DEHAM	Customer8	DEBRV	BR2
QMT200002858	SHP2800129	DEHAM	Customer10	DEBRV	BR2
QMT200002858	SHP2800130	DEHAM	Customer10	DEBRV	BR2

Regards

[Anonymous]

Hello @Anonymous,

I've worked with your formula but still can't receive expected results. Please take a look at my file in below link and let me know what I am doing wrong.

Shipments vs Quotes

many thanks in advance!

[ChrisMendoza]

@Anonymous,

I am unsure about your expected result. When I use the following, I get the below results:

QuoteID1 = 
IF (
    AND (
        Shipments[Departure] >= RELATED ( Quotations[Start Date] ),
        Shipments[Departure] <= RELATED ( Quotations[Expiry Date] )
    ),
    LOOKUPVALUE (
        Quotations[QuoteID],
        Quotations[Branch], Shipments[Branch],
        Quotations[Department], Shipments[Department],
        Quotations[Local Client], Shipments[Local Client],
        Quotations[SrvLevel], Shipments[Srv Level],
        Quotations[Origin], Shipments[Origin],
        Quotations[Destination], Shipments[Destination]
    )
)

QuoteID2 = 
LOOKUPVALUE (
        Quotations[QuoteID],
        Quotations[Branch], Shipments[Branch],
        Quotations[Department], Shipments[Department],
        Quotations[Local Client], Shipments[Local Client],
        Quotations[SrvLevel], Shipments[Srv Level],
        Quotations[Origin], Shipments[Origin],
        Quotations[Destination], Shipments[Destination]
)

I am also unsure of performance as well.

[Anonymous]

Hello @ChrisMendoza,

Trying to follow your guideline but below error occurs:

what am I doing wrong?

Thanks, Tomas

[ChrisMendoza]

@Anonymous, 

Seems like you do not have a relationship between the two tables. When I loaded your sample tables, Power BI established the relationship as the below for me:

Is your's not similar?

For Reference RELATED ( ) 

[Anonymous]

Hi @ChrisMendoza,

Relation is working if LocalClient is unique but if I add another Quotation for the same client but different Start and Expiry dates (very last  row in below table) then relations dissapearring and your formula is not working.

BranchDepartmentQuoteIDLocal ClientSrvLevelOriginDestinationStart DateExpiry Date

BR2	241	QMT200002851	Customer1	LIF	NLRTM	DEBRV	2018.06.02	2018.06.30
BR2	241	QMT200002852	Customer2	FIL	NLRTM	DEBRV	2018.06.02	2018.06.15
BR2	241	QMT200002853	Customer4	LIL	KRPUS	DEBRV	2018.06.02	2018.06.30
BR2	241	QMT200002854	Customer5	LID	KRPUS	DEBRV	2018.06.02	2018.06.30
BR2	241	QMT200002855	Customer7	LIF	DEHAM	DEBRV	2018.06.02	2018.06.30
BR2	241	QMT200002856	Customer8	LIF	DEHAM	DEBRV	2018.06.02	2018.06.30
BR2	241	QMT200002857	Customer9	LID	CNSHA	DEBRV	2018.06.03	2018.06.15
BR2	241	QMT200002858	Customer10	LIF	DEHAM	DEBRV	2018.06.01	2018.06.30
BR2	241	QMT200002859	Customer10	LIF	DEHAM	DEBRV	2018.07.01	2018.07.31

Any thoughts how to solve such case?

Thanks in advance!