Match two tables with multiple criteria

[Setname]

Hi,

I'm facing a problem to map values between two tables.

Basically there are two tables, one is "Voyage" and the other one is "Job", and my objective is to see how many jobs we didn't manage to get per port.

For example, in the "Voyage" table, Vessel "A", Destination Port "ASD" and sailling date is on Jan 6th 2019, and in the "Job" table, it needs to look up the same Vessel which is "A", then look up in Loading Port "ASD", and if the two condition matched, the third criteria is the Job Perform date in "Job" table must not be more than two months (eg 60 days) than the "Sailling date" in 'Voyage" table.

If any of the 3 criteria is not matched, it counts as "one" opportunity.

In the end, I want to have an analysis on how many opportunities per Destination Port in the "Voyage" Table.

Can anyone please help me with this? I have listed below the two tables for your reference.

Thanks!

Voyage Table:

Vessel	Destination Port	Sailing Date (mm/dd/yyyy)
A	ASD	1/6/2019
B	FSD	3/5/2019
C	KLD	5/6/2019
D	DFR	3/4/2019
A	ACF	2/5/2019
B	ASD	4/5/2019
C	FSD	3/17/2019
D	KLD	3/29/2019
E	DFR	5/1/2019
F	ACF	3/9/2019
B	DFR	4/12/2019
C	ACF	5/4/2019
D	ASD	3/21/2019
E	FSD	3/23/2019
F	KLD	3/14/2019
C	FSD	1/9/2019
D	KLD	2/16/2019
A	DFR	2/9/2019
B	ACF	5/3/2019
C	DFR	4/14/2019
D	ACF	4/4/2019
E	ASD	4/21/2019
F	FSD	3/4/2019
B	KLD	2/22/2019
C	FSD	4/4/2019
F	KLD	2/28/2019
B	DFR	3/6/2019
C	ACF	4/13/2019
D	DFR	4/3/2019
E	ACF	4/20/2019
F	ASD	3/14/2019
C	FSD	2/26/2019
D	KLD	2/18/2019

Job Table:

Vessel	Loading Port	Job perform date (mm/dd/yyyy)
A	ASD	2/3/2019
B	FSD	4/6/2019
C	KLD	8/4/2019
D	DFR	7/4/2019
E	DFR	6/1/2019
F	ACF	5/9/2019
B	ACF	6/9/2019
C	FSD	7/1/2019
D	ASD	4/1/2019
E	FSD	5/4/2019
F	KLD	3/23/2019
C	FSD	3/4/2019
D	KLD	3/1/2019
A	ACF	3/4/2019
B	FSD	4/4/2019
C	ASD	8/4/2019
D	FSD	7/4/2019
E	KLD	6/1/2019

[venal]

@Setname 

I tried to re-pro the issue from my end and uploaded the file.

3rd creteria, i am not clear. so tried to do it upto days wise data.

If you have any concerns, please let us know.

If this post helps, then please consider Accept it as the solution to help the other members find it more
If this post was helpful may I ask you to mark it as solution and click on thumb symbol?

[Setname]

Thanks @venal , it's a good idea but the base we should use is Voyage table not Job table to do the merge.

However, Merge method doesnt really solve the issue because it will create duplicate rows after merging.

For example as below, it will create duplicate two rows (Vessel B, Port FSD and same Sailing Date), however my objective is to only showing the earliest Job perform date which is April 4th 2019, not together with April 6th 2019. Do you have any idea how to resolve this? Thansks.

Vessel	Destination Port	Sailing Date (mm/dd/yyyy)	Job.Vessel	Job.Loading Port	Job.Job perform date (mm/dd/yyyy)
B	FSD	3/5/2019	B	FSD	4/6/2019
B	FSD	3/5/2019	B	FSD	4/4/2019

[v-jayw-msft]

Hi @Setname ,

I'm working on your data and I want to know how we deal with the duplicate data in Voyage table and Job table?

For Example:

Voyage Table:

Vessel	Destination Port	Sailing Date (mm/dd/yyyy)
C	FSD	3/17/2019
C	FSD	1/9/2019
C	FSD	4/4/2019

Job Table:

Vessel	Loading Port	Job perform date (mm/dd/yyyy)
C	FSD	7/1/2019
C	FSD	3/4/2019

Best Regards,

Jay

[Setname]

Hi @v-jayw-msft ,

Thanks for helping.

It's not really duplicated rows because the Sailing date in Voyage table and Job performed date in Job table is different.

For example, in the Voyage table, Vessel C could depart to Destination Port FSD a few times across the year.

Similarly, we could do the loading job for Vessel C a few times in a year.

I understand that there could be a problem like below, it's just an illustration and not in my file:

Voyage Table:

Vessel	Destination Port	Sailing Date (mm/dd/yyyy)
C	FSD	3/17/2019
C	FSD	2/9/2019
C	FSD	5/1/2019

Job Table:

Vessel	Loading Port	Job perform date (mm/dd/yyyy)
C	FSD	4/1/2019

In the example above, based ont he criteria, the Job performed on April 1st 2019 which mapped both Sailing date on March 17th and Feburary 9th 2019 (less than 60 days). In such a case, they are not considered an Opportunity. However, the Sailing date is May 1st 2019 but there is no job performed within 60 days on the same vessel same port, so this is considered as an Opportunity.

Result (by creating a new column):

Vessel	Destination Port	Sailing Date (mm/dd/yyyy)	Opportunity
C	FSD	3/17/2019	No
C	FSD	2/9/2019	No
C	FSD	5/1/2019	Yes

Thanks again!

[v-jayw-msft]

Hi @Setname ,

In you scenario, we can use Merge Queries feature in Query Editor, please check following steps as below:

1. Merge these two tables as a new table and expand table:

2. Click Close&Apply and create measures:

    Measure = IF(ISBLANK(MAX(Merge1[Job.Job perform date (mm/dd/yyyy)])),1,IF(DATEDIFF(MAX(Merge1[Sailing Date (mm/dd/yyyy)]),MAX(Merge1[Job.Job perform date (mm/dd/yyyy)]),MONTH)>2,1,0))

    Measure 2 = SUMX(Merge1,[Measure])

3. Result would be shown as below:

BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Setname]

Thanks @v-jayw-msft  it's a good idea.

However, Merge method only solve half of the issue because it will create duplicate rows after merging.

For example as below, it will create duplicate two rows (Vessel B, Port FSD and same Sailing Date), however my objective is to only showing the earliest Job perform date which is April 4th 2019, not together with April 6th 2019. 

After merging, the column Vessel and Destination port, Sailing date should be unique, and I just want to look up the earliest Job perform date after the Sailing date from Job table. Do you have any idea how to resolve this? Thanks.

Vessel	Destination Port	Sailing Date (mm/dd/yyyy)	Job.Vessel	Job.Loading Port	Job.Job perform date (mm/dd/yyyy)
B	FSD	3/5/2019	B	FSD	4/6/2019
B	FSD	3/5/2019	B	FSD	4/4/2019

[v-jayw-msft]

Hi @Setname ,

That's exactly what I said about the duplicate data above.

If you just want to look up the earliest Job perform date, we need to do some Group By operations on Job table before merge these two tables:

And modify measure 1 as below:

Measure 1 = IF(ISBLANK(MAX(Merge1[Job.earliest date])),1,IF(DATEDIFF(MAX(Merge1[Sailing Date (mm/dd/yyyy)]),MAX(Merge1[Job.earliest date]),MONTH)>2,1,0))

Result would be shown as below:

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Setname]

Thanks @v-jayw-msft  can you send me the updated pbix file?

[v-jayw-msft]

Hi @Setname ,

Here's the Pbix, hopefully works for you.

BTW, if you just want the earliest date in Voyage table, just do the same Group By operation on it.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Setname]

Hi @v-jayw-msft ,

I understand your objective by Grouping before the merge but we can't simply pick the earliest date, the logic is wrong.

Firstly of all, like I said earlier, we have to use voyage table as the base, to map against job table, so basically once Vessel name and Port is matched, if there are mutiple job performed date, last step is to pick the earliest date right after the sailling date. If you group the job table by selecting earliest date before the mapping, you accidently move forward the last step into the first and this is not the right logic.

Do you have other method to achieve this?

[v-jayw-msft · Accepted solution]

Hi @Setname ,

Sorry for the delay.

Please modify the measures as below:

Measure =
VAR a =
    CALCULATE (
        MIN ( Merge1[Job perform date] ),
        FILTER (
            ALL ( Merge1 ),
            Merge1[Vessel] = MAX ( Merge1[Vessel] )
                && Merge1[Destination Port] = MAX ( Merge1[Destination Port] )
        )
    )
VAR b =
    IF (
        ISBLANK ( MAX ( Merge1[Job perform date] ) ),
        BLANK (),
        IF (
            MAX ( Merge1[Sailing Date] ) > MAX ( Merge1[Job perform date] ),
            "NA",
            IF (
                MAX ( Merge1[Job perform date] ) <> a
                    && MAX ( Merge1[Sailing Date] ) < a,
                "NA",
                a
            )
        )
    )
RETURN
    IF (
        ISBLANK ( b ),
        1,
        IF (
            b = "NA",
            0,
            IF (
                DATEDIFF (
                    MAX ( Merge1[Sailing Date] ),
                    MAX ( Merge1[Job perform date] ),
                    MONTH
                ) > 2,
                1,
                0
            )
        )
    )

Measure 2 = SUMX(Merge1,[Measure])

Result would be shown as below:

BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Setname]

Thanks Jay, exactly what I need!

[Setname]

Hi, is there anyone can help with this pls? Thanks.