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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors