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 prima...
  • 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.

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.