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.

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi driplytics 
What dax code did you use? which error did you get? which column connects the two tables?

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
        Community 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.