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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.