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

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?

       

       

       

       

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        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.