Match two tables with multiple criteria

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

Need Help

Anonymous

6 years ago

Hi Anonymous ,

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.

Anonymous
Not applicable
6 years ago
Hi Anonymous ,

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!
- Anonymous
  Not applicable
  6 years ago
  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.
  - Anonymous
    Not applicable
    6 years ago
    Thanks Anonymous 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

Forum Discussion

Match two tables with multiple criteria

Related content

Sequential Order column based on multiple criteria

DAX FILTER with multiple criteria

Multiple Criteria Filter

Dynamic Filter creation based on multiple criteria

Date difference with multiple criteria

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

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