Finding MIN date in a table within a group

[Dudeman]

Hi. I have the following flight booking data. I'm looking for a Dax formula to pick out the actual destination. See below for what I'm looking for and some IF logic. RDU is the actual destination, the other rows are either connections or the flight back home from RDU.

							LOOKING FOR..
BookingNumber	departingAirport	arrivalAirport	arrivalTime	MaxArrival	ArrivalDate	Arrive/Connect	Actual Destination
12345	BUF	ATL	6/10/21 7:00 AM	6/10/21 10:00 AM	6/10/21	Connection	N/A
12345	ATL	RDU	6/10/21 10:00 AM	6/10/21 10:00 AM	6/10/21	Arrival	RDU	IF MaxArrival = arrivalTime AND Arrive/Connect = Arrival
12345	RDU	ATL	6/14/21 11:00 AM	6/14/21 2:00 PM	6/14/21	Connection	N/A
12345	ATL	BUF	6/14/21 2:00 PM	6/14/21 2:00 PM	6/14/21	Arrival	N/A

I'm thinking I need to find the minimum arrival date within the booking with the maximum arrival time/date to flag RDU as the destination.

MaxArrival is a caluclated measure:

MaxArrival =  CALCULATE(MAX( 'Legs'[arrivalTime]),

ALLEXCEPT ('Legs','Legs'[BookingNumber]),

GROUPBY('Legs','Legs'[ArrivalDate]))

I'm grouping by BookingNumber (via ALLEXCEPT) as there are other booking numbers in my full dataset (this is just a sample).

Arrive/Connect is a calculated column:

Arrive/Connect = IF([Max Arrival] = 'Legs'[arrivalTime], "ArrivingDest", "Connection")

*I need a Dax solution as my data is coming from a Live Connection/direct query PBI dataset. I can't transform the data.

I have tried various calculated columns and measures but get various errors.

Thank you!

[V-pazhen-msft · Accepted solution]

@Dudeman 
There are only a few more rows I can just type in. That was my mistake, I have thought about the multiple booking numbers, but forgot to bring it into the formula. Change the Result Destination measure to the following should work.

Result Destination = IF([MaxArrival]=MINX(FILTER(ALLSELECTED(Legs),[BookingNumber]=MAX([BookingNumber])),[MaxArrival]) && [ArriveConnect]="Arrival",MAX([arrivalAirport]))

Best Regards
Paul Zheng _ Community Support Team

[Dudeman]

Sorry Paul. One last question. That measure works, but I now need to composite in a country table with a relationship to the airport code. Result Destination needs to be in a calculated column in order for me to link the detination airport code to my country table (I think?).

Can you help with what the calculated column formula would be for the solution you provided?

Thanks again.

[Dudeman]

Amazing. Thank you SO much @V-pazhen-msft Paul. Really appreciate your help on this.

If it's not too much to ask, and no rush at all, would you mind explaining what the formula is doing? It's a bit difficult for me to understand all the pieces and how they're working together here.

Thanks again.

[V-pazhen-msft]

@Dudeman 

To  achieve your desired result, you would need to create the 3 measures:

MaxArrival = CALCULATE(MAX( 'Legs'[arrivalTime]),ALLEXCEPT ('Legs','Legs'[BookingNumber]),GROUPBY('Legs','Legs'[ArrivalDate]))

ArriveConnect = IF([MaxArrival]= MAX([arrivalTime]), "Arrival", "Connection")

Result Destination = IF([MaxArrival]=MINX(ALL(Legs),[MaxArrival]) && [ArriveConnect]="Arrival",MAX([arrivalAirport]))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Dudeman]

@V-pazhen-msft Paul, thank you so much - very close but not quite. This is more what I'm looking for though.

This works with a single booking number, but when I apply it to my larger dataset with more booking numbers I only get a sinlge destination. I need to constrain the Min of the Max Arrival time to each booking number. How would you adjust your solution if there are more than one booking?

Thanks so much.

[Dudeman]

Here is an expanded example dataset.. for some reason it won't take my new table so here's an image of it:

12345 = RDU

23456 = YYZ

34567 = SEA

[Greg_Deckler]

@Dudeman I would think a measure like:

Measure =
  VAR __ArrivalAirport = MAX('Table'[ArrivalAirport]
  VAR __ArriveConnect = MAX('Table'[Arrive/Connect])
  VAR __ArriveDate = MAX('Table'[ArrivalDate])
RETURN
  IF(__ArriveDate = [Max Arrival] && __ArriveConnect = "Arrival",__ArrivalAirport,"N/A")

[Dudeman]

@Greg_Deckler 

Thanks for the reply Greg.

When I add your measure to the table a black box pops up and then disappears (with a title bar saying something about SQL) and then it breaks my table visual saying it can't display the visual.

Thoughts?

Perhaps there's another way to do this, a shorter way, where I don't need to use the Arrive/Connect calc'd column? Maybe I can say something like flag the row as the destination where the minimum MaxArrival date for a booking (6/10/21 10:00AM) equals the arrivalTime? Something like that could return RDU.