Help with DAX Lookup

[AOW1]

Hi there

I have limimted DAX knowledge but think this can be achieved through a DAX lookup, but I need help to write it

I have a table of holiday bookings containing all the separate lines of the itinerary (flight, accommodation, transfer etc) for each booking. I want to show the routing for flights by airline and booking volume which i have achieved but what i am struggling with is connecting flights. It will show me that London to Dubai is a popular route but we know that Dubai is largely a connecting airport and the end destination is often Africa, Asia etc - so the challenge is to show connecting flights correctly as London - Johannesburg.

What i think i need is to lookup the 'Book ID' and search for the next flight line by 'Itin date' and then if the origin of this flight is the same as the destination of the first, and is within say 1 day, then we can assume it is a connecting flight and to return the destination of the second flight?

Perhaps I need a calcualted column to show what the next flight is as well?

And then after that I can deduce whether this is connecting flight Yes/No and if Yes then show the London-Johannesburg and if no show the London-Dubai?

Some sample data:

Any help would be greatly appreciated - thank you!

Arthur


EDIT

Hi @v-yadongf-msft - thanks for your comment.  Do these extra columns help at all?

Here are the criteria I think I need:

- Lookup by BookId

- ItinType is Flight

- Origin of next flight is same as destination of first (e.g. Dubai, Dubai)
- Next flight is less than or equal ItinDate of first flight +1

[AOW1]

Does anyone have any ideas about how to achieve this one?

thanks 

[v-yadongf-msft]

Hi @AOW1 ,

In order to better understanding your demands and give the right solution, could you please tell me what's your expected output?

Thanks for your efforts & time in advance.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.