Lookupvalue between tables with partial text

[fhfon]

Hello all,

I need a help with the situation below.

I have two table:

Table1:

Part number	Description	Status
1252	Bearing	Critical
993	Filter	Non critical

Table2:

Description	Status
Bearing 3cm stainless steel
Exhaust HEPA Filter

What I need is kind a "lookupvalue" with partial text that will bring the status from the table1 to table2, but the issue is that description in table2 has more information.

Please, HELP me!  🙂

Att.

Fernando Nobre

[amitchandak · Accepted solution]

@fhfon , Try a new column in Table2 like

Maxx(filter(Table1, search(Table1[Description], Table2[Description],,0) >0 ), Table1[Status])

[smpa01]

@fhfon  you can write a measure like this

Measure =
MAXX (
    FILTER (
        CROSSJOIN (
            VALUES ( 'Table 2'[Description] ),
            SELECTCOLUMNS (
                SUMMARIZE ( 'Table 1', 'Table 1'[Description], 'Table 1'[Status] ),
                "_description", [Description],
                "status", [Status]
            )
        ),
        CONTAINSSTRING ( [Description], [_description] )
    ),
    [status]
)

[fhfon]

Thanks! But I could not make this work.

[amitchandak · Accepted solution]

@fhfon , Try a new column in Table2 like

Maxx(filter(Table1, search(Table1[Description], Table2[Description],,0) >0 ), Table1[Status])

[fhfon · Accepted solution]

Thank you!

This worked as I wanted.