Forum Discussion
Search in a text column for values in another table using DAX
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
jackjanice , Try a new column in Data table
maxx(filter(RegionTag , search(RegionTag [region], Data[Tag],,0)>0)RegionTag[region])
3 Replies
- amitchandak
Super User
jackjanice , Try a new column in Data table
maxx(filter(RegionTag , search(RegionTag [region], Data[Tag],,0)>0)RegionTag[region])
Thank you amitchandak very much, this is exactly what I want!
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."
