Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
alexeisenhart
Resolver I
Resolver I

Calucated Columns for Aggregate Values From Another Dataset

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 😃

1 ACCEPTED SOLUTION
alexeisenhart
Resolver I
Resolver I

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.

View solution in original post

2 REPLIES 2
alexeisenhart
Resolver I
Resolver I

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
Super User
Super User

I think you want to use the MAXX function.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.