Forum Discussion
Helper IFinding MIN date in a table within a group
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:
Arrive/Connect is a calculated column:
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
8 Replies
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
DudemanAmazing. Thank you SO much Anonymous 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.
- 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.
- Greg_Deckler
Community Champion
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
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.
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
Anonymous 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
