cancel
Showing results for
Did you mean:
Highlighted
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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

Re: Finding max of multiple rows for every customer

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

Proud to be a Datanaut!

2 REPLIES 2
Super Contributor

Re: Finding max of multiple rows for every customer

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

Proud to be a Datanaut!

Frequent Visitor

Re: Finding max of multiple rows for every customer

Thank you. That solved the problem.

Announcements

Back to School Contest

Engage and empower students with Power BI!

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 240 members 3,302 guests
Recent signins: