A Common Modelling Scenario That Got Me Stumped

[connelvalentine]

I have not found any place which answers this seemingly common scenario.

Say it's the travel industry and my fact table has travel itineraries with the Origin Airport and Destination Airport as seperate fields. The dimension table would be an Airport Table.

How would I model this data considering the following business requirements:

REQ 1-The report needs to visualize travel itineraries in a table at the lowest grain (each itinerary), showing the details of both origin and destination airports in the same row.

REQ 2-Any measures created from the itineraries fact table (example Count of itineraries, Count of late itineraries) need to be filtered by any field in the Airport dimension table.

REQ 3-Measures like Count of Itineraries when filtered by an airport would count  itineraries regardless of whether the airport was an origin or destination in the itinerary. 

Every post I come across on role playing dimensions speaks of dates as the dimension table (Order Date vs Ship Date) and using USERELATIONSHIP in DAX to create additional measures. I presume that solution would not work for text-based role playing dimensions like the example I gave above.

So my question is, what is the best way to model the Itineraries fact table and Airports dimension table  to meet the business requirements above.

I considered one airport table with an active/inactive relationship but then I would struggle with REQ 1.

I have considered two separate airport tables (Origin Airports and Destination Airports) to link to each field in the fact table but then I would struggle with REQ 2 / REQ 3.

I also considered having both of the above - Origin / Destination airports table that has relationships to the fact table AND a standalone Airports table (cannot link to Origin/Destination airport as it will create an ambiguous model) where specific measures will be built using TREATAS? This solution seems a little far fetched at first glance.

Thanks in advance for your support.

[connelvalentine]

Thanks for the recommendation John T. Although having the airport address in the fact table won't be recommended as in my case, the address is a large set of characters and modelling that in the fact table may hurt performance.

i have gone ahead with my third option but created a connected locations table to both a origin airport table and destination airport table with an inactive relationship to the destination airport table. I would have to explicitly create specific measures by location that is cross filtered to the locations table. 

[v-echaithra]

Hi @connelvalentine ,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.

[johnt75 · Accepted solution]

For REQ1 you could add any fields you wanted to display into the fact table, e.g. Arrival Name, Dest Name, Arrival Code, Dest Code.

For filtering the measures you could have a single, disconnected, airports table and create a calculation group with a calculation item like

Airport Filter =
VAR SelectedAirports =
    VALUES ( Airports[Code] )
VAR Result =
    CALCULATE (
        SELECTEDMEASURE (),
        KEEPFILTERS ( 'Fact'[Arrival Code]
            IN SelectedAirports
                || 'Fact'[Destination Code] IN SelectedAirports )
    )
RETURN
    Result

Use this as a filter on any visuals where you want that behaviour, or potentially as a report level filter if you want it  everywhere.