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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
SHDJason
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. 

2 REPLIES 2
Anonymous
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:

 

IF(COUNTROWS(FILTER(Mentions,IFERROR(SEARCH([Source],[Url]),BLANK())))>1,TRUE(),FALSE())

 

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

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.