Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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's avatar
    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's avatar
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.

VesselDestination PortSailing Date (mm/dd/yyyy)Job.VesselJob.Loading PortJob.Job perform date (mm/dd/yyyy)
BFSD3/5/2019BFSD4/6/2019
BFSD3/5/2019BFSD4/4/2019
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks Anonymous  can you send me the updated pbix file?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

         

        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?