lookupvalue with number or text

[dogburalHK82]

Hi 

I am trying to make Table 3 from Table 1 and Table 2. 

Since they are not completely matching, I am not quite sure how I can do a lookupvalue (or other dax).

Any help would be much appreciated. 

[tamerj1 · Accepted solution]

@dogburalHK82 
Please try

Table3 =
GENERATE (
    VALUES ( Table1[Item Number] ),
    SELECTCOLUMNS (
        FILTER ( Table2, CONTAINSSTRING ( Table1[Item Number], Table2[Item Number] ) ),
        "Category", Table2[Category]
    )
)

[devanshi]

lookupvalues=LOOKUPVALUE('table3','Table1[item number],'Table2[category])

[tamerj1]

Hi @dogburalHK82 

what is the name of the 2nd column of table2?

[dogburalHK82]

@tamerj1 

let me upload with all names in.

[tamerj1 · Accepted solution]

@dogburalHK82 
Please try

Table3 =
GENERATE (
    VALUES ( Table1[Item Number] ),
    SELECTCOLUMNS (
        FILTER ( Table2, CONTAINSSTRING ( Table1[Item Number], Table2[Item Number] ) ),
        "Category", Table2[Category]
    )
)

[dogburalHK82]

@tamerj1 Great thank you.

If I want to create one calculated column next to Item number column in Table 1, what should I use?

[tamerj1 · Accepted solution]

@dogburalHK82 

Try

Table3 =
MAXX (
    FILTER ( Table2, CONTAINSSTRING ( Table1[Item Number], Table2[Item Number] ) ),
    Table2[Category]
)

[dogburalHK82]

@tamerj1 had tried but got this error

[tamerj1]

@dogburalHK82 

You mentioned that you want to create a calycolumn in table1 but seems that you are creating a measure!

[dogburalHK82]

@tamerj1 my apologies, i tried it in the different table. It works perfect.