DAX: Help returning a Value in a table based on MAX Date and another Matching field value

[Jeremyh]

I Have a two Tables, 'Table 1' contains 1 record per Consignment (order) and 'Table 2' which contains many Scan Transactions with fields [Count of Scans], [Scan Branch] (Location), and [Scan Date]. These two table have a 1:Many relationship on the [Consignment Number] field.

I need help with a DAX Statement that That returns the Value of the [Count of Scans] for the record with the Latest [Scan Date] and matching [Scan Branch] and [Destination Branch], If no matches return BLANK

From the above example, I would expect to the result to be 5

Appreciate any help.

[Jeremyh]

Thanks @Ashish_Mathur  Really Appreciate your help

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Do you want a measure or a calculated column formula solution?

[Jeremyh]

Hi @Ashish_Mathur I'm wanting a Measure. I have already added a couple of measure that work correclty, returning the First and Last Dates RELATED to the Destination Branch in Table 1. But I'm struggling to return the Value for Count of Scans. In the Attached Report, I want to reutn:

ConsignmentNumber | Last Scan Count

ABC0034274    |    5
ABC0034275    |    6

Link to Project File: BI Project 

Appreciate any help

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached my PBI file.

Hope this helps.