Need more efficient "containsstring" method

[JustSayJoe]

I have a table (Advertising) with these three columns.

• Campaign
• Ad Set
• Ad Name

Each of the columns are text strings that use multiple underscores as delimiters. The number of delimiters is not always the same.

Examples of a value for each column:

• Campaign
  • Facebook_RandomTitle_125
• Ad Set
  • Interest_Outdoors_125
• Ad Name
  • Video_Launch_30seconds_Captions_125

That underscore 125 (_125) value in the examples above is the value I'm trying identify to create a separate calculated column with just the 125 value. That value is used to join to another table to identify the Owner for filtering purposes in my report.

Currently I'm running a way too long DAX formula to build that calculated column, which first looks at the Campaign fields with a CONTAINSSTRING to search for _125 and return the value 125 if found (all wrapped in a SWITCH ( TRUE() statement). It does this for every new Owner I add, so a new row is added to lookup that new number. It then searches all the Ad Sets and Ad Names after that (the order of searching Campaign, Ad Set, and then Ad Name is important for my purposes).

As you can imagine this DAX is getting way too large and slow with all the containsstring searches on text fields. Does anybody have a better more efficient solution they could suggest for me to accomplish this?

[v-pagayam-msft]

Hi @JustSayJoe ,

Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster. 

If we don’t hear back, we’ll go ahead and close this thread.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you.

[v-pagayam-msft]

Hi @JustSayJoe ,
we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.
If our response addressed your query, please mark it as Accept Answer and click Yes if you found it helpful.
If you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-pagayam-msft]

Hi @JustSayJoe ,
May I ask if you have gotten this issue resolved? If it is solved, please mark the helpful reply or share your answer and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
Thank you.

Regards,
Pallavi.

[Ashish_Excel]

Hi,

Using Power Query, extract everything after the last delimiter.

[ryan_mayu]

then what about not using containsstring function.

extract 125 in the PQ

or use DAX to extract 125

Column = mid('Table'[column1],FIND("_",'Table'[column1],1,0)+1,100)

[JustSayJoe]

Thank you for the response. My problem is, the values in the column do not maintain a standard naming convention, so there's a lot of cleanup after using PQ to split the column at the right most delimiter.

I'm thinking I may need to bear down and set aside a bunch of hours to manually clean up a ton of historical data...

[ryan_mayu · Accepted solution]

then pls provide some sample data and the expected output.