Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

New Column based on values from a different table

Hi all, 

I have a table with customer ID and postcode they live in (table A) and another table with postcodes and regions (Table B). I would like to create a column in table A with the Region related to the postcode. 

i have tried the merge function but it looks like the final table is not right! Also playing with a lookupvalue function, however I am getting the "a table if multiple values was supplied where a single value was expected" error

Any help would be greatly appreciated

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    If the postcode column has unique vaule in the table B, the lookupvalue should work fine as following calculate column

     

    Region = LOOKUPVALUE('Table B'[regions],'Table B'[postcode],[postcode])

    If one postcode has multi region, you can give me the expected result based on my sample data.

     

     

    BTW, pbix as attached.

     

    Best regards,

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi,

    You will need to join the two tables using postcode field and use below dax to create a calculated column in Table A to get the region field from Table B.

    Region = RELATED( Table B[Region] )

    Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Tried it, but I am getting an error saying that the region field is not recognised as there is no relationship between the two tables, even thoug hI created the relationship already. 

      Is there anything iam missing?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi,

        Can you share your report pbix?

        Thanks.

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    If the postcode column has unique vaule in the table B, the lookupvalue should work fine as following calculate column

     

    Region = LOOKUPVALUE('Table B'[regions],'Table B'[postcode],[postcode])

    If one postcode has multi region, you can give me the expected result based on my sample data.

     

     

    BTW, pbix as attached.

     

    Best regards,

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