Link reference table twice without duplicating data.

[nigel_s]

Given the following tables

Trip

ID

FromLocationID

ToLocationID

Location

ID

Name

City

I want to be able to use the location name for both the From and To in Trip but you can't create two active relationships. I can use Lookup, but if I wanted to reference City and Name, that becomes a pain, I really want to reference the entire table. I can add the Location table twice and link From and To separately but I think this duplicates the data in the Location table. 

Is there a way to include a reference table twice but make the second a virtual copy of the first?

What's the best way to do this? In my actual model Location is used multiple times with several fact tables, with multiple location fields.

Thanks in advance for your help.

[DataInsights]

@nigel_s,

You can create an inactive relationship and use the DAX function USERELATIONSHIP to activate the inactive relationship. In this example, the active relationship would be between Location[ID] and Trip[FromLocationID]. The inactive relationship would be between Location[ID] and Trip[ToLocationID]. Example measure:

Count Trip ID - To Location =
CALCULATE (
    COUNT ( Trip[ID] ),
    USERELATIONSHIP ( Location[ID], Trip[ToLocationID] )
)