Data manipulation

Here is how i would fix it.

I have skipped modelling so the Departure and Arraival matches on all lines, with the same booking ref.

Instead i have made a new Routing, that shows the true departure, and true arrival for a flight on all lines.

You will need to make 3 new columns.

First columns.

True departure = IF(

LOOKUPVALUE('Table'[Arrival ];'Table'[Arrival ];'Table'[Depature ];'Table'[Booking ref];'Table'[Booking ref])=BLANK();

"Departure";

"Not Departure"

)

 

the first column outputs Departure if this line has the first departure of the travel, and Not Departure, if this is not the first departure

 

I replicate this with arrival, so second column should look like this.

True Arrival = IF(

LOOKUPVALUE('Table'[Depature ];'Table'[Depature ];'Table'[Arrival ];'Table'[Booking ref];'Table'[Booking ref])=BLANK();

"Arrival";

"Not Arrival"

)

 

Now i make the third column called True Routing.

True Routing = CONCATENATE(

LOOKUPVALUE('Table'[Depature ];'Table'[True departure];"Departure";'Table'[Booking ref];'Table'[Booking ref]);

CONCATENATE("/";

LOOKUPVALUE('Table'[Arrival ];'Table'[True Arrival];"Arrival";'Table'[Booking ref];'Table'[Booking ref])

))

 

This will output LHR/LAX for John Doe in the data example.

 

If it is important for you to have the Departure and Arrival on seperate columns as well, then take the 2 LOOKUPVALUE formuals in the third coulumn and add them in their own columns.

 

 

Do note, this will only work, if all travel for the booking ref is in the same day, otherwice it will still seperate the value, unless you have worked this in your meassures, or simply don't add the date column in your matrix.

 

Hope this help. 🙂