Search in a text column for values in another table using DAX

[jackjanice]

Hi experts,

I have two tables, one is called "Data", there's a columns called "tags", the values in this column is comma separated strings, each string stands for a tag. Another table is called RegionTag table with only one column in it called Tag, this table contains all region tags.

Some sample data in the two tables look like below,

Data table:

Id	Tags
A101	INCLUDE,APPLE,US
A102	EXCLUDE,UK,ORANGE
A203	ASIA,PEAR
A104	PEACH

RegionTag table:

Tag

US

UK

ASIA

I need to create a "Region" column in Data table. In this table, I only need the region tag for each row. The Region column for above sample table should look like below,

Id	Tags	Region
A101	INCLUDE,APPLE,US	US
A102	EXCLUDE,UK,ORANGE	UK
A203	ASIA,PEAR	ASIA
A104	PEACH	(blank)

Is this something can be done in a DAX?

Thanks,

Jack

[amitchandak · Accepted solution]

@jackjanice , Try a new column in Data table

maxx(filter(RegionTag , search(RegionTag [region], Data[Tag],,0)>0)RegionTag[region])

[Anonymous]

@amitchandak Hey Amit - are you able to help with below question. It is similar issue to above. But it is for Tabular Model DAX not PBI DAX. hence the solution here isn't working there. It throws error -

"The function MAXX takes an argument that evaluates to numbers or dates and cannot work with values of type String."

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Lookupvalue-for-a-Search-String-in-Tabular-Mo...

[jackjanice]

Thank you @amitchandak very much, this is exactly what I want!