Forum Discussion
calculated column using another table scan
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
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
matchedCategoryIf this solved your issue, please mark it as the accepted solution. ā
5 Replies
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
Hi vijaykaali811
Can you please try the below steps?
Use the below DAXresult = LOOKUPVALUE(Table2[Column1], Table2[Column2], Table1[Column], "not found")If this answers your questions, kindly accept it as a solution and give kudos.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
matchedCategoryIf this solved your issue, please mark it as the accepted solution. ā
it worked like a charm . thank you a lot . just removed subsitute section as per my requirement
