Find value from related table based on extra filter conditions

[Anonymous]

Hi,

I have been working with PBI for over 2 years, but really can't find a solution to a seemingly simple problem.

I have 2 tables: 'Customers' and 'Invoices' which are related 1 to * based on [CustomerID]. The 'Invoices' table has besides [CustomerID] also [InvoiceID], [Amount] and [InvoiceDate] as columns. I now want to add a calculated column to 'Customers' which has is the [InvoiceID] of the most recent [InvoiceDate] with an [Amount] > x . 

Any help on how to construct the DAX formula would be highly appreciated!

[Arul]

@Anonymous ,

It would be better if you share some sample data or the shcema?

Thanks,

Arul

[Anonymous]

Hi @Arul , thanks for reviewing my question, and I indeed should have included some sample data to give a better picture of the problem. But I think I just solved it:

LatestInvoice =

var LatestInvoiceDate = CALCULATE(max(Invoices[InvoiceDate]),Invoices[Amount] > x)

var InvoiceNo = CALCULATE(SELECTEDVALUE(Invoices[InvoiceID]),Invoices[InvoiceDate]=LatestInvoiceDate)

return

InvoiceNo

At least it seems to work. I would have expected that a more elegant solution was possible (probably is) based on RELATEDTABLE, but I just couldn't make it work.
Anyway, sorry to have bothered you.