cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Creating a Column that Searches for Strings in a Filtered Second Unrelated Table

I have a fairly convoluted problem that I could solve in a few minutes with Javascript but seem to be unable to solve with DAX or PowerBI. Heres my setup;

 

I have several tables, but of most importance to this problem, is a Search Term Data table and a Keyword Data table. These tables are both connected to a Campaigns table via a many to one relationship on a Campaign ID related connection.

 

I am trying to determine, for each entry in Search Term Data, if a Search Term already appears as a Keyword in the Keyword Data table. Complicating this challenge is the need for both Search Term Data and Keyword Data tables be filtered in scope to a specific Campaign ID for this query. If the value does already exist returning TRUE or "Yes" is an ideal goal.

 

I believe for this to work as intended, I need to create a new column so that each row of Search Term Data has its own result. When I searched around, most of the similar questions here did not work or account for this specific use case with both tables needing to be filtered first.

2 REPLIES 2
v-diye-msft
Community Support
Community Support

Hi @Anonymous 

 

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution. if not, please share more details about your question, we'd like to provide further support. thanks!

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
belisqui
Advocate IV
Advocate IV

A lot going on, so i'm not sure if I understood the scenario.

 

A few months ago I created a search keyword across multiple fields & table by playing around with the model.

 

Have you thought about merging Table.[Search Term Data] with Table.[Keyword Data] in the edit queries?

If you do that you can have both columns next to each other and easily do anything you want.

 

  1. create a new custom column = Text.From([CampaignID]) & "|" & Text.From([SearchTerm]) in Table.[Search Term Data]
  2. Then another in Table.[Keyword Data] = Text.From([CampaignID]) & "|" & Text.From([Keyword])
  3. Merge (Full Outer) both tables on the custom column.
  4. Create a new CampaignID column to combine both CampaingIDs in case of empty rows.
  5. Map that to Campaign table.

The end result can look like this:

(I merged Keyword into Search Term) I would send you this pbix if I could, but there isn't an option for that.

Capture.PNG

 

 

 

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors