Create new column using Dax to pull data from 2 table to make the new column

[Anonymous]

Hi

I have 2 tables. in which I need to create a new Column where its pulling data from both tables. Table 1 is the main table that has the key information that is related to Table 2 Primary Item Nmber. Table 1 has some part numbers that are longer in nature the Dax formula needs to look at table 2 Engineering item number to find the same part number to makethe new column 

Table 1

 

Table 2 

Table 1 with new column

[VahidDM]

Hi @Anonymous 

try this to add a new column:

column=

var AA= len([CPARTN])

Var BB= lookupvalue(table 2[Primary Item Number], table 2[Engineering item number], table[CPARTN])

return

if(AA=7,AA,BB)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

The Dax expression works fine that you gave but forgot to add that I have duplicate  values in the table so i get the error - A table of multiple values was supplied where a single value was expected . I appreciate any help. Thanks

[Anonymous]

Hi VahidDM

I was able to create the column with your information and it was what I am looking for but wanted to see where the column value is 7 that it would be equal to CPARTN number. 

[VahidDM · Accepted solution]

Hi @Anonymous 

This is the correct code:

column =
VAR AA =
    LEN( [CPARTN] )
VAR BB =
    LOOKUPVALUE(
        'table 2'[Primary Item Number],
        'table 2'[Engineering item number], table[CPARTN]
    )
RETURN
    IF( AA = 7, [CPARTN], BB )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Hi VahidDM

The Dax expression works fine that you gave but forgot to add that I have duplicate  values in the table so i get the error - A table of multiple values was supplied where a single value was expected. I appreciate any help. Thanks