lookup Value from another table without relationship

[RanjanThammaiah]

Hi All,

I wanted to know if there is any possibility of searching a keyword from 1st table and getting the lookup value from the second table.

1st Table

Name

Definition of climate

Climate Infrastructure Opportunities

Sustainability-Climate Risk Support

ARDIAN Adaptation Analysis

2nd Table
Keyword	Tag	Rank
Climate	Sustainability	1
Sustainability	Sustainability	2

Wherever the 2nd table keyword matches with the 1st table. the values should be populated from the 2nd table(Tag Column)

1st table with Tag
Name	Tag
Definition of climate	Sustainability
Climate Infrastructure Opportunities	Sustainability
Sustainability-Climate Risk Support	Sustainability
ARDIAN Adaptation Analysis	Other

[v-zhangti]

Hi, @RanjanThammaiah 

You can try the following methods.

Column = 
IF (
    SEARCH ( "Climate", [Name],, 0 ) <> 0,
    LOOKUPVALUE ( Table2[Tag], Table2[Keyword], "Climate" ),
    "Other"
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Ahmedx · Accepted solution]

column

column = COALESCE(
CALCULATE(MAX('Table_2'[Tag]),
    FILTER ( 'Table_2', CONTAINSSTRING ( 'Table_1'[Name], 'Table_2'[Keyword]) )),"Other")

[Ahmedx]

and it's already in DAX
https://1drv.ms/u/s!AiUZ0Ws7G26RhhcCNBmSOBFgkRry?e=fOxJt0

[Ahmedx]

see attached, i solved your problem in power query
https://1drv.ms/u/s!AiUZ0Ws7G26RhhY5L7x0e_GvMC0F?e=Ays6kb

[FreemanZ]

hi @RanjanThammaiah 

try like:

Tag =

LOOKUPVALUE(

    Table2[Tag],

    Table2[Keyword],

    Table1[Name]

 )