Calucated Columns for Aggregate Values From Another Dataset

[alexeisenhart]

I'm sure that this is a basic question but I can't find the solution anywhere online.

I have two datasets:

• BoxMovement - This is the set of where boxes are moving through a system
  • BoxID, Movement Datetime, From Location, To Location
• ShippedBox - This is the set of boxes that have been shipped to customers
  • BoxID, Ship Datetime, Order ID, etc

There are many BoxMovement for one ShippedBox.

I'd like to add a calculated column to ShippedBox that contains the last time that the box was moved to a particular location. In SQL, I'd express it as

SELECT * FROM (

SELECT BoxID, LocationTo, MAX([Movement Datetime]) FROM BoxMovement bm GROUP BY BoxID, LocationTo

) LastArrival

JOIN ShippedBox sb on sb.BoxID = bm.BoxID

WHERE LocationTo = 'Conveyor 123'

I have achieved the grouping by BoxID and LocationTo by creating a reference to the BoxMovement dataset and then grouping by these two columns (and adding the aggregate columns that I needed), but I'm not sure how to query to find the specific rows that I need now.

My 3rd table, the grouped table, has these columns: BoxID, LocationTo, Count, Last Datetime.

How can I add a calculated column to ShippedBox that grabs the Last Datetime from the grouped table from the row that matches the BoxID and LocationTo?

Or, would it be better to add a calcuated column to ShippedBox that uses DAX to compute the aggregate without using the grouping table? I'm not sure how to do this either 😃

[alexeisenhart · Accepted solution]

Thanks for your response Greg. MAXX might work also but I got it working with a Calculate and Filter. I don't know why it took so long for the use of these functions to sink in.

= CALCULATE(MAX(BoxMovement[Datetime])
     , FILTER(

          BoxMovement, BoxMovement[BoxID] = ShippedBox[BoxID] && LEFT(BoxMovement[From Location], 5) = "ABCDE")
     )

)

I found that LOOKUPVALUE was also useful to find the other fields from the record matching the datetime that was returned.

[Greg_Deckler]

I think you want to use the MAXX function.