Forum Discussion

Cadams's avatar
Cadams
Icon for Microsoft Employee rankMicrosoft Employee
2 years ago
Solved

Return Primary Partner based on 2 parameters (Date + ID)

Hi all,

 

I've been trying to figure this out for days now without any luck. I get close, but not the exact answer.

Here's the example data:

 

 

What I'm looking to do is find the primary partner (output in table 1) who has created the latest (by created date) lead against the correct opportunity ID (table 2).

So for example, out of the 5 leads against opportunity ID "123" in table 2, Walmart is the latest lead to have been created and therefore, the "primary partner" field in table 1 is populated with Walmart.

I've tried using various formulas to get to the answer, e.g.:

 

CALCULATE(MAX([Partner Name]),FILTER(Table1 [opportunity ID] = table 2 [opportunity ID && max(table 2 created date)))

Or 

MAXX(FILTER(Table 2,MAX(table 2[Created Date]) && Table 1[Opportunity ID]=table 2 [opportunity ID),table 2 [partner name])

When I change the output from partner name to created date, it provides the correct date, which is the latest created lead, but when I change it back to partner name, it doesn't return the correct partner name.
 
Any help would be greatly appreciated. Please let me know if you need any additional context.
 
Thanks!

 



  • Irwan's avatar
    Irwan
    2 years ago

    Hello Cadams ,

     

    please check if this accomodate your need.

     

    Primary Partner =
    var _MaxDate =
    MAXX(
        FILTER(
            'Table 2',
            'Table 1'[Opportunity ID]='Table 2'[Opportunity ID]&&
            MAX('Table 2'[Created Date])
        ),
        'Table 2'[Created Date]
    )
    Return
    MAXX(
        FILTER(
            'Table 2',
            'Table 2'[Created Date]=_MaxDate&&
            'Table 1'[Opportunity ID]='Table 2'[Opportunity ID]
        ),
        'Table 2'[Partner Name]
    )

     

    1. your maxx looks good, however the expression of maxx can be changed into date that returned a correct date as you mentioned above.

    2. do maxx again with the date that previously calculated (which already return as accurate value) and matching opportunity ID. If you dont want to use maxx again, you can use CALCULATE as replacement.

     
    Primary Partner =
    var _MaxDate =
    MAXX(
        FILTER(
            'Table 2',
            'Table 1'[Opportunity ID]='Table 2'[Opportunity ID]&&
            MAX('Table 2'[Created Date])
        ),
        'Table 2'[Created Date]
    )
    Return
    CALCULATE(
        MAX('Table 2'[Partner Name]),
        FILTER(
            'Table 2',
            'Table 2'[Created Date]=_MaxDate&&
            'Table 1'[Opportunity ID]='Table 2'[Opportunity ID]
        )
    )

     

    And yes, if expression in first maxx is put directly into partner name, it would return into inaccurate value (from my experience, it because your data has skip date which is no 01/01/2024). However, since this is from my experience, my reason might be wrong.

     

    Hope this will help you.

    Thank you.

3 Replies

  • Irwan's avatar
    Irwan
    Icon for Super User rankSuper User
    2 years ago

    Hello Cadams ,

     

    please check if this accomodate your need.

     

    Primary Partner =
    var _MaxDate =
    MAXX(
        FILTER(
            'Table 2',
            'Table 1'[Opportunity ID]='Table 2'[Opportunity ID]&&
            MAX('Table 2'[Created Date])
        ),
        'Table 2'[Created Date]
    )
    Return
    MAXX(
        FILTER(
            'Table 2',
            'Table 2'[Created Date]=_MaxDate&&
            'Table 1'[Opportunity ID]='Table 2'[Opportunity ID]
        ),
        'Table 2'[Partner Name]
    )

     

    1. your maxx looks good, however the expression of maxx can be changed into date that returned a correct date as you mentioned above.

    2. do maxx again with the date that previously calculated (which already return as accurate value) and matching opportunity ID. If you dont want to use maxx again, you can use CALCULATE as replacement.

     
    Primary Partner =
    var _MaxDate =
    MAXX(
        FILTER(
            'Table 2',
            'Table 1'[Opportunity ID]='Table 2'[Opportunity ID]&&
            MAX('Table 2'[Created Date])
        ),
        'Table 2'[Created Date]
    )
    Return
    CALCULATE(
        MAX('Table 2'[Partner Name]),
        FILTER(
            'Table 2',
            'Table 2'[Created Date]=_MaxDate&&
            'Table 1'[Opportunity ID]='Table 2'[Opportunity ID]
        )
    )

     

    And yes, if expression in first maxx is put directly into partner name, it would return into inaccurate value (from my experience, it because your data has skip date which is no 01/01/2024). However, since this is from my experience, my reason might be wrong.

     

    Hope this will help you.

    Thank you.

  • Cadams's avatar
    Cadams
    Icon for Microsoft Employee rankMicrosoft Employee
    2 years ago

    Irwan , thank you so much. I've spent hours and hours trying to figure all combinations out, going through so many different forums and you got it in one!

    Massive massive thank you!