Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Find value from related table based on extra filter conditions

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!

Need Help

2 Replies

  • Arul's avatar
    Arul
    Icon for Super User rankSuper User
    3 years ago

    Anonymous ,

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

    Thanks,

    Arul

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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.