cancel
Showing results for 
Search instead for 
Did you mean: 
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 IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

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

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.