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 ,

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks Jay, exactly what I need!