searching for text in a separate table

[CristinaB]

Hi everyone,

I need help in the following situation. I have two data sets that are not related to each other. The first one is actually a Bookings report by Order Number

Order Number	Customer	Amount
123456	Epsilon.COM	10000
456789	Beta.com	5000

And the second one is a CRM Extract which should include the order number but unfortunately without any naming convention

Order Number Cloud

0000123456-aabs

qwerty456789qwerty

In my first table i want to search by each order number in the second table.

In excel i used the following formula: =IF(COUNTIF(Order_Number_Cloud;"*"&[@[Order Number]]&"*")>0;"in CRM Cloud";"not in CRM Cloud")

Can i do something similar in POwer BI taking into consideration that i don't have any relationship between the 2 tables ?

Thank you so much !

Cristina

[v-jayw-msft]

Hi @CristinaB ,

I found a similar topic. It might help you.

https://community.powerbi.com/t5/Desktop/Search-text-for-keywords-from-another-separate-table-Search... 

Best Regards,

Jay

[amitchandak]

@CristinaB , Create a new column in table 1

if(countx(filter(Table2,search(Table[Order Number], table2[Order Number],1,0)>0), table2[Order Number])+0>0,"in CRM Cloud","not in CRM Cloud")

[CristinaB]

@amitchandak Thank you! i do get an error message and don't know exactly how should i proceed.

formula:

CRM ENg = if(COUNTX(FILTER(vwCRMOrders, search(Orders[Order Number], vwCRMOrders[Order Number],1,0)>0), vwCRMOrders[Order Number]+0>0), "in CRM Cloud", "not in CRM Cloud")

Error: The function COUNTX cannot work with values of type Boolean.

Any advice on what to do next ?