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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rspilevo
New Member

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

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:

rspilevo_0-1718396666882.png

 

 

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

 

rspilevo_0-1718395767321.png

 

1 ACCEPTED SOLUTION
v-yohua-msft
Community Support
Community Support

Hi, @rspilevo 

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

Based on your information, I create a table:

vyohuamsft_0-1718588486633.png

 

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:

vyohuamsft_1-1718589260304.png

 

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.

View solution in original post

2 REPLIES 2
v-yohua-msft
Community Support
Community Support

Hi, @rspilevo 

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

Based on your information, I create a table:

vyohuamsft_0-1718588486633.png

 

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:

vyohuamsft_1-1718589260304.png

 

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
Solution Sage
Solution Sage

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~

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.