Looking up field values with inactive relationships between tables

[GVTioluxe]

Hi

I have a few tables in my model and one of them is the Shipment Plan Table which has field of Origin Port Code and Destination Port Code of the Order. I have a Port Master from which I would like to get the Port Name.

The Port Table has active relationship with the Sales Order Table which also have Origin and destination Ports. 

Since the Shipment Plan is connected with Sales Order, trying to make active connection with Port Table is causing error of ambiguity due to which it doesnt allow.

hence i have left the the relationships as inactive between Shipment Plan and Port and would like to use the userelationship dax to create 2 measures one for Origin Port Name and another for destination Port Name.

I tried following 2 options - 

Option1 -

Origin Port Name = Lookupvalue(Port[PorName], Port[Portcode],shipmentplan[portcode])

here the last field of port code in shipment plan doesnt come up to complete the dax.

Option 2 -

Origin Port Name = Calculate(Max(Port[portname]),Userelationship(shipmentplan[portcode],port[portcode])

this is returning the last value of port names alphabetically.

can someone guide me on thr right way to solve this problem?

regards

[amitchandak]

@GVTioluxe , are you trying to create a new column, In that case, it will only return one value, if there is more than one value then min and max need to be used.

Refer : https://www.youtube.com/watch?v=czNHt7UXIe8

[GVTioluxe]

Hi

No. I am trying to create a measure to return the Port name, which i will use in the Table/ Matrix Visual

regards

[v-shex-msft]

Hi @GVTioluxe,

Can you please share some dummy data with the original data structure to help us clarify your data structure and test coding formula?

How to Get Your Question Answered Quickly 
Regards,

Xiaoxin Sheng