Forum Discussion

michellepace's avatar
michellepace
Icon for Resolver III rankResolver III
7 years ago
Solved

Create a column based on a lookup in another table

Hi, I've spent the last hour trying to understand this and google around. I must be missing something completely because it simply cannot be that difficult. Alright, I have the below two tables:   ...
Need Help
  • dax's avatar
    dax
    7 years ago

    Hi michellepace,

    You could try below

    Column = LOOKUPVALUE('Table2'[AccNumber],'Table2'[AccNumber],'Table 1'[AccNumber])*1000

    Best Regards,
    Zoe Zhi

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

  • michellepace's avatar
    michellepace
    6 years ago

    Hi, it's amazing how I can make something so simple so complicated :). The RELATED function worked for me. Just posting this pic here for anyone else who may find it useful. 

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi,

Given that there is relationship between 2 tables, you can use RELATED function in DAX to achieve this:

Use the below code to create the column in Table 2.

Main = RELATED( Table1[AccNumber] )

Thanks.

michellepace's avatar
michellepace
Icon for Resolver III rankResolver III
7 years ago

Hi Anonymous , thank you for your reply. I've tried to follow your advice but obviously I'm getting something wrong... can anyone please tell me why I am getting this error? As far as I can see my syntax is correct...?

 

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi michellepace,

    You could try below

    Column = LOOKUPVALUE('Table2'[AccNumber],'Table2'[AccNumber],'Table 1'[AccNumber])*1000

    Best Regards,
    Zoe Zhi

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

    • michellepace's avatar
      michellepace
      Icon for Resolver III rankResolver III
      6 years ago

      Hi, it's amazing how I can make something so simple so complicated :). The RELATED function worked for me. Just posting this pic here for anyone else who may find it useful. 

       

      • HelenaX's avatar
        HelenaX
        Icon for Helper I rankHelper I
        5 years ago

        Thank you! So helpful!!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

    That formula has to be written in the AccBalances Table and it should be a calculated column formula (not a measure)

    • michellepace's avatar
      michellepace
      Icon for Resolver III rankResolver III
      7 years ago

      Hi Ashish, thanks again for your reply. What I am trying to do is get a column which is in the AccBalances Table, into my Hierarchy table. So... I'm not really sure why I would then be writing the formula in the table (AccBalances) which already has the column I need. Sorry, I am completely confused here. Can you please detail exactly what you mean?

       

      And in fact, although I may be wrong, I found that the Lookup function only works when I am on the 'many' side of the cardinality looking up into a table on the '1 side' of the cardinality. That is, there is no ambiguity for the lookup function. This makes sense to me. Am I correct or imagining it?

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        Hi,

        Share the link from where i can download your PBI file.