Newbie trying to combine data/metrics for text that is the same (like pivot table)

[rspilevo]

I'm new to DAX and what I have is marketing campaign data with metric associated. For example, we have multiple campaigns with names that include "DEN" or "SEA" for Denver and Seattle. What I'd like to do is find all the campaign names that contain "DEN" and combine them into a new field that is just "Denver" and combines all the data for the campaigns that share DEN in the name. 

In the screenshot you'll see there are multiple campaigns with DEN in the name and I've brought this into PowerBI and would like to consolidate all these into one line for all Denver campaigns. So it would combine cost, impressions, clicks etc. Is there a way to do this?

I use Data Studio and this is how I'd do it there with regular expression:

And then when I use that field it would have the values as denver, portland. 

[v-yohua-msft · Accepted solution]

Hi, @rspilevo 

Thanks for the reply from @xifeng_L , please allow me to provide addition:

Based on your information, I create a table:

Then create a calculated column and try the following dax:

Campaign City = 
SWITCH (
    TRUE (),
    CONTAINSSTRING ( [Campaign], "SLC" ), "Salt Lake City",
    CONTAINSSTRING ( [Campaign], "DEN" ), "Denver",
    CONTAINSSTRING ( [Campaign], "PDX" ), "Portland",
    CONTAINSSTRING ( [Campaign], "SEA" ), "Seattle",
    "Other"
)

Here is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xifeng_L]

Hi @rspilevo 

You can create a new column and use the SEARCH function to accomplish your goal.

Calculated Column =
SWITCH (
    TRUE (),
    SEARCH ( "SLC", 'Table'[Campaign], 1, 0 ), "Salt Lake City",
    SEARCH ( "DEN", 'Table'[Campaign], 1, 0 ), "Denver",
    SEARCH ( "PDX", 'Table'[Campaign], 1, 0 ), "Portland",
    SEARCH ( "SEA", 'Table'[Campaign], 1, 0 ), "Seattle",
    "Other"
)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~