One to many relationship based on partial match (using regex)?

[hairycoo]

I'm very new to PowerPivot but very excited to be using it! Here is my problem:

My Data table has:

[Keyword] , [Source] , [Visits] columns

Keyword column has duplicate values.

Examples of values found in [Keyword]:
google
google analytics
how to use google analytics
metrics

The common link between the first 3 values is "google". What I would like to do is to create
an additional column to assign a label "branded" to all keywords including the word "google". I might have a KeywordDefinition for this purpose.

My KeywordDefinition table has:

[Keyword] , [KeywordCategory]

Keyword column has only unique values so this would be the key.

Example:

[Keyword] [Keyword Category]
google branded


Creating a relationship between the tables and adding a calculated column in my Data table with the Related('KeywordDefinition'[Keyword Category]) function assigns the label ONLY to Keywords matching "google" exactly. Is there a way to assign this label to ALL keywords in my Data table containing "google"? I tried changing the value in my KeywordDefinition table like this:

[Keyword] [Keyword Category]
*google* branded

Doesn't seem to work.

Can it be done? Any ideas?


Thanks!

 

[Laurent C]

From the title of your post, I assume you want to be able to associate one row in your table to several keywords.

In that case changing the context filter in your measures is more appropriate than creating a calculated column, since calculated relationships are not supported by PowerPivot.

You could do something along the line
=CALCULATE(Your calculation here,
GENERATE(KeywordDefinition,
FILTER(Data,
NOT(ISERROR(SEARCH(KeywordDefinition[Keyword],Data[Keyword])))
)
)
)

This expression has not been tested for performance, and there are other ways to do this.