Measure to get latest values by related column

[Anonymous]

Hi all

I have a 2 tables, Items and Contracts.

A Contract can have multiple Items. 

In my table visual, i display some infos of the item, and then some columns/infos about the contract.

My problem is, that Item 888 has 2 contracts, so power bi makes 2 rows as it cant summarize it. 

I now want to create a measure, to show item 888 only once, with contract details based on the latest end date column (in contracts).

(Image upload wont work, see there: https://s19.directupload.net/images/200220/trx34bkz.png)

Any ideas?

[Greg_Deckler · Accepted solution]

Generally you solve that by getting the MAX or MAXX of your Date. Then you can use that within the measure to retrieve the correct (latest) information about whatever it is you are trying to retrieve information about. So, the pattern is MAX or MAXX to get your Date and then FILTER for the item and that date again using something like MAXX or LOOKUPVALUE to retrieve the particular value in the column.

[Anonymous]

Hi @Greg_Deckler 

Thanks for your answer, worked for me.

Code for anyone interested:

latest_ID = CALCULATE(MAX(Contracts[ID]); FILTER(Contracts; Contracts[EndDate] = [latest_EndDate]))