Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jackjanice
New Member

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:

IdTags
A101INCLUDE,APPLE,US
A102EXCLUDE,UK,ORANGE
A203ASIA,PEAR
A104PEACH

 

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,

IdTagsRegion
A101INCLUDE,APPLE,USUS
A102EXCLUDE,UK,ORANGEUK
A203ASIA,PEARASIA
A104PEACH(blank)

 

Is this something can be done in a DAX?

 

Thanks,

Jack

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@jackjanice , Try a new column in Data table

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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@jackjanice , Try a new column in Data table

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

Anonymous
Not applicable

@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...

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.