cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Joernolav Frequent Visitor
Frequent Visitor

Finding max of multiple rows for every customer

Hi.

 

I have two tables. One named External_Contact which contains customers and one table named G_MainL which contains invoices  and a column named paydate.

 

They are connectd with columns Kundenr and Contid one-to-many.

 

I need a column in External_Contact that show the last payment date for each customer.

 

I have tried the following for a new column but get an error.

 

last_payment = LOOKUPVALUE(Max(G_MAINL[Paydate]); External_Contact[Kundenr]; G_MainL[Contid])

 

I get the error: " Lookupvalue expects a column reference as argument number 1."

 

what am I doing wrong?

 

Thank you for your help.

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Finding max of multiple rows for every customer

Hi @Joernolav

 

Since you have a relationship between the two tables, you can use this formula as a calculated column in External_Contact:

 

last_payment =
CALCULATE ( MAX ( G_MainL[Paydate] ) )

Since this is calculated within the row context of External_Contact, the CALCULATE results in context transition, effectively adding the current row's customer to the filter context, which in turn filters G_MainL.

MAX ( G_MainL[Paydate] ) is then evaluated over the rows of G_MainL corresponding to that customer.

 

Cheers,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




2 REPLIES 2
OwenAuger Super Contributor
Super Contributor

Re: Finding max of multiple rows for every customer

Hi @Joernolav

 

Since you have a relationship between the two tables, you can use this formula as a calculated column in External_Contact:

 

last_payment =
CALCULATE ( MAX ( G_MainL[Paydate] ) )

Since this is calculated within the row context of External_Contact, the CALCULATE results in context transition, effectively adding the current row's customer to the filter context, which in turn filters G_MainL.

MAX ( G_MainL[Paydate] ) is then evaluated over the rows of G_MainL corresponding to that customer.

 

Cheers,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Joernolav Frequent Visitor
Frequent Visitor

Re: Finding max of multiple rows for every customer

Thank you. That solved the problem.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 1,697 guests
Please welcome our newest community members: