measure to get field value from lookup table using inactive relationship with Fact Table

[GVTionale]

Hi

I have in my Customer Order Fact Table, 2 fields Origin Port ID and Destination Port ID. I have the Port lookup Table which has the Port ID, Port Name, Country etc. I have created Active relationship with the Origin Port ID and an inactive relationship with the Destination Port ID.

Can someone help me to get the correct Dax measure to get the Port Name for destination port ?

regards

[Anonymous]

Hi @GVTionale ,

Check the measures and relationship as below and see if the result is what you want.

Measure = CALCULATE(MAX('Port lookup Table'[Port Name]),USERELATIONSHIP('Port lookup Table'[Port ID],'Table'[Origin Port ID]))

Measure 2 = CALCULATE(MAX('Port lookup Table'[Port Name]),USERELATIONSHIP('Port lookup Table'[Port ID],'Table'[Destination Port ID]))

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[AllisonKennedy]

@GVTionale  You could use: 

COLUMN Destination Port = LOOKUPVALUE(PortTable[Port Name], PortTable[Port ID], FactTable[Destination Port ID])

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[GVTionale]

Hi Alison,

The destination port has inactive relationship with port lookup table for which i saw the solution posted in following link to use calculate dax with userelationship. 

https://carldesouza.com/power-bi-dax-userelationship-multiple-dates/

My query is whatdax to use in the calculate DAX to lookup the portname if i apply the DAX shown in the post

Regards