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
CristinaB
Regular Visitor

searching for text in a separate table

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 NumberCustomerAmount
123456Epsilon.COM

10000

456789Beta.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

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
amitchandak
Super User
Super User

@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")

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

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.