topic Re: Return Primary Partner based on 2 parameters (Date + ID) in DAX Commands and Tips

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

Cadams — Tue, 02 Jul 2024 14:09:17 GMT

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!

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

Irwan — Wed, 03 Jul 2024 00:46:23 GMT

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.

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

Cadams — Wed, 03 Jul 2024 09:06:30 GMT

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!

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

Irwan — Wed, 03 Jul 2024 16:47:08 GMT

Hello Cadams

Glad to be a help.

Thank you.