topic Re: Newbie trying to combine data/metrics for text that is the same (like pivot table) in DAX Commands and Tips

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

Anonymous — Fri, 14 Jun 2024 20:24:34 GMT

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.

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

xifeng_L — Sat, 15 Jun 2024 09:39:20 GMT

Hi Anonymous

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~

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

Anonymous — Mon, 17 Jun 2024 02:01:00 GMT

Hi, Anonymous

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.