calculated column using another table scan

[vijaykaali811]

Hi

I have two table column 

table1 

========

product

description 

qty

category >> calculated field 

table2 

pattern - contains patters  eg . product*kids , dress*adult .etc

category -  Kids , Adult Dresses 

i want to fill table1 category column  , by taking description checking with table2 pattern column . if match found then copy table2.category to table1.category.  how can i achieve in power bi.  and relationship cannot be created using this 

I achieved this in excel using xlookup 

i have table with  description column . i have to filter the category column basedon following action 

 1. take description and check on another  table [with patterns and category ]. pattern column and it match is found then get the category  

[mdaatifraza5556]

Hi @vijaykaali811 

Can you please try the below steps?

Use the below DAX 

result = LOOKUPVALUE(Table2[Column1], Table2[Column2], Table1[Column], "not found")

If this answers your questions, kindly accept it as a solution and give kudos.

[anilelmastasi · Accepted solution]

But user says that only category column.

You an try this @vijaykaali811 ,

Category =
VAR descText = table1[description]
VAR matchedCategory =
MAXX(
FILTER(
table2,
SEARCH(
SUBSTITUTE(table2[pattern], "*", ""),
descText,
1,
0
) > 0
),
table2[category]
)
RETURN
matchedCategory

If this solved your issue, please mark it as the accepted solution. ✅

[vijaykaali811]

it worked like a charm . thank you a lot . just removed subsitute section as per my requirement 

[anilelmastasi]

Hello @vijaykaali811 ,

Could you please share a sample value from Table2's pattern column?

[vijaykaali811]

Table1

product,description ,qty

10,  product dress for kids ,  10

11,  casual dress ware for men xx brand ,  11

10,  formal dress ware for men xx brand ,  10

table2

pattern ,  category 

product*kids , Kids 

casual dress*men,  Casual men

formal dress*men,  formal men