Forum Discussion

driplytics's avatar
driplytics
Frequent Visitor
4 years ago
Solved

Create a custom column with value from another table

Hi, i need help with this. i have a flight data table with column name AirportFrom  and another AirportTo, columns has IATA code(Airport Code).                                 ...
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, driplytics 

    Delete the relationship/add a new seperate table and then try calculated columns as below:

    AirportOrigin = LOOKUPVALUE('Airport Data'[name],'Airport Data'[iata_code],'Dataset'[AirportFrom])
    Airport Destination = LOOKUPVALUE('Airport Data'[name],'Airport Data'[iata_code],'Dataset'[AirportTo])

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

driplytics's avatar
driplytics
Frequent Visitor
4 years ago

thank you tamerj1 for ypur response.

Here's Dax i used to Create the 2 Custom columns

AirportOrigin = CALCULATE(
LOOKUPVALUE('Airport Data'[name],'Dataset'[AirportFrom],SELECTEDVALUE('Dataset'[AirportFrom]),
'Dataset'[AirportFrom])
Airport Destination = CALCULATE(LOOKUPVALUE('Airport Data'[name],'Dataset'[AirportTo],SELECTEDVALUE('Dataset'[AirportTo]),'Dataset'[AirportTo]))
 
 
This is the Error i got.
 
 
 
Here are the connected column on both tables.
 
 
 
 
 
 
 
 
 
My Goal is to retrieve the Airport Name from the Airport Data and store it in a custom column created on the Dataset table. 
 
for Reference here's the link to my Datasets. 
  • driplytics's avatar
    driplytics
    Frequent Visitor
    4 years ago

    thanks tamerj1 , i think you are close to the solution.

    but instead of returning same Iata_code (Eg in the pics below, ATL is an Airport code), i want the name returned (Hartsfield Jackson Atlanta International Airport) on the custom columns.

     

     

     

     

     

     

     

     

     

     

     

     

    • v-easonf-msft's avatar
      v-easonf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi, driplytics 

      Delete the relationship/add a new seperate table and then try calculated columns as below:

      AirportOrigin = LOOKUPVALUE('Airport Data'[name],'Airport Data'[iata_code],'Dataset'[AirportFrom])
      Airport Destination = LOOKUPVALUE('Airport Data'[name],'Airport Data'[iata_code],'Dataset'[AirportTo])

      Best Regards,
      Community Support Team _ Eason
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.