Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Advocate II
Advocate II

Matching a single value to a possible value in another table?

Hi. I have been trying to figure this out and I finally give up. 


Here is the situation:


I have a table of website traffic with a long list of full URLs.

I have another very short table with list of root domains that we are focusing on.


I would like to create a new column in the first table that flags where the URL matches the second table. It does not need to be an exact match, just a root domain match.


The problem that I am stuck on is that the FIND function only accepts a single value to match to, it does not iterate through a list of possible matches.


FIND("single expression", [URL]).  Where I need "single expression" to iterate through a list of possible values.


I thought nesting the FIND function inside a FILTER function to compare one row at a time would work, but I get stuck on one value and it does not iterate through the list of possible matches.


All the iterating functions that I could find are good for math, but not for text or logic functions.


Maybe there is a way to do this in M using the Text.Contains function?


I'm sure there is a simple way to do this that I'm just not understanding. I appreciate any help. Thanks. 

Not applicable

You can do this in dax.


You can use FIRSTNONBLANK combined with a filter:


FIRSTNONBLANK(FILTER(VALUES('Table to Search'[Field]),SEARCH('Table to Search'[Field],'Table with Main Data'[Field],1,0)),1)

Thanks @Anonymous!  


I actually woke up in the middle of the night with this solution:




Which works, but I think yours is more elegant!  


For some reason I need to wrap my SEARCH and FIND functions in IFERRROR because I keep getting errors on the not found value. 



Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors