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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
adilk
Helper I
Helper I

Unable to run multiple text search in string to create a new column, even via Switch

Hi,

I have a column [Ad Set Name] that has text in it such as millenials, students. I want to create a new column where it searches for the main keyword and then assigns a value in the new column.

e.g. If Ad Set contains the word 'students', then Segment (new column) should = "Students"

Else,

If it contains "creators", then Segment = "Creators"

Here's the formula I'm trying but it shows all as unknown.

Segment = 
SWITCH (
true (),
SEARCH ( "Students", 'Table'[Ad Set Name], 1, 0 ) = 1, "Students",
SEARCH ( "creators", 'Table'[Ad Set Name], 1, 0 ) = 1, "Creators",
"UNKNOWN"
)


Any help with this.

Thanks.

1 ACCEPTED SOLUTION
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @adilk,

 

How about this formula?

 

Segment =
SWITCH (
    TRUE (),
    SEARCH ( "Students", table1[Ad Set Name], 1, 0 ) > 0, "Students",
    SEARCH ( "Creators", 'table1'[Ad Set Name], 1, 0 ) > 0, "Creators",
    "UNKNOWN"
)

Unable to run multiple text search in string to create a new column, even via Switch.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
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

18 REPLIES 18
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @adilk,

 

How about this formula?

 

Segment =
SWITCH (
    TRUE (),
    SEARCH ( "Students", table1[Ad Set Name], 1, 0 ) > 0, "Students",
    SEARCH ( "Creators", 'table1'[Ad Set Name], 1, 0 ) > 0, "Creators",
    "UNKNOWN"
)

Unable to run multiple text search in string to create a new column, even via Switch.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

@v-jiascu-msft @Anonymous

Hi Dave, the formula you provided worked. Can you please help me understand what was wrong with the Isnumber(search()) approach we had used earlier?? thx

Hi @adilk,

 

That's because Search() always returns numbers. Isnumber() will always be true. Please refer to: https://msdn.microsoft.com/en-us/library/ee634235.aspx.

 

>>>Returns the number of the character at which a specific character or text string is first found, reading left to right. Search is case-insensitive and accent sensitive.

 

 

Best Regards!

Dale

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

@v-jiascu-msft 

in my last formula, I had 

ISNUMBER(SEARCH ("Students", table1[Ad Set Name],1,0 )), "Students",

Is the 0 within the Search part messing up the formula, because if DAX doesn't find the word "students", it returns a 0 and since Search is wrapped within IsNumber, it  always become true?

 

Hi @adilk,

 

No, the return type of Search() is numbers. You can see it from the snapshot below. The number 12. Pay the attention to the formula I used.

Unable to run multiple text search in string to create a new column, even via Switch2.jpg

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

 

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

@v-jiascu-msft

I'm not fully clear on why your formula worked.

In traditional Excel, i'm clear that the Search function provides the ordinal position of what is being searched for. 

In the Dax Switch formula, it is set to TRUE,

when we use the formula you provided, the search function only provides the ordinal position but does not explicitly return a TRUE/FALSE. How is the switch statement able to then assign the segment?

Hi @adilk,

 

The blue part is one complete parameter of Switch that will return True/False. Not the Search function. 

Segment =
SWITCH (
    TRUE (),
    SEARCH ( "Students", table1[Ad Set Name], 1, 0 ) > 0, "Students",
    SEARCH ( "Creators", 'table1'[Ad Set Name], 1, 0 ) > 0, "Creators",
    "UNKNOWN"
)

Best Regards!

Dale

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

@v-jiascu-msft

Got it! thanks a lot.

Anonymous
Not applicable

Hi @adilk,

 

For me the DAX formula works: 

 

Segment = SWITCH(TRUE()
;SEARCH("Student";Table2[AdSetName];1;0) = 1; "Student";
SEARCH("Creator";Table2[AdSetName];1;0) = 1; "Creator";"UNKNOWN")

Capture.PNG

 

As you can see in the capture above the formula returns the expected results. Since I did'nt know what your data looked like I assumed something like this. If it still does'nt work for you perhaps you could share some dummy data so we can search for the error.

 

Regards,

L.Meijdam

@Anonymous 

@n your example, you used semi colons instead of comma as the separator.

Sample data attached here:

Column A (Ad Set)                                   Column B (Segment) - What I want

Ad date - Segment [Creators]                   Creators
Ad date - Segment [Millenials]                  Millenials

@BeemsC

Anonymous
Not applicable

Hi @adilk,

 

The use of commas or semicolons depends on the configuration of your computer/ Power BI so that is not making a difference. But your output only returns "UNKNOWN" ? I am sure you already checked but did you type the matching word correctly in your DAX-formula, also check on capital letters for example. 

 

In your first example you only included "Student" and "Creator" in your data sample I see that you used "Millenials" since you didd'nt include a search for "Millenials" it is expected that it returns "UNKNOWN" for these records. (could you perhaps create a bit more sample data since this is kinda confusing)

 

Regards,

L.Meijdam

@Anonymous

Thanks for pointing out on comma vs semi colon.

I thought Search function was case insensitive and it wouldn't matter if the column had Creators or creators, both should get classified as 'Creators' in the new column.

 

Ad Set	                                                New Column
Ad Date - [Creators]	                        Creators
Ad Date - [Old device owners]	        Old device owners
Ad date - [Purposeful Millennials]	        Purposeful Millennials
Ad date - [Students]	                        Students

 

P.S. - I went into query editor and created a conditional column. That method works but I want to know about the Switch DAX function  method as well.

 

Anonymous
Not applicable

Hi @adilk,

 

I am glad you found a way that works for you, about the SWITCH function here you have a few sources that might help you to reach a better understanding 🙂

 

https://msdn.microsoft.com/en-us/library/gg492166.aspx  ----- Documentation about SWITCH function

https://community.powerbi.com/t5/Desktop/SWITCH-statement-in-DAX-using-a-quot-between-this-value-and... --- A user explains how you can use the SWITCH function in this community post

https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/ --- post about the SWITCH function

https://powerpivotpro.com/2012/06/dax-making-the-case-for-switch/ --- post about the SWITCH function

 

I am affraid I am not able to tell you why it did'nt work for you since the formula worked on my end ... Anyway I hope these links can help you out a bit.

 

Regards,

L.Meijdam

 

 

@Anonymous

In your example, the reference column data began with what I want to search for (e.g. Creators), it's what I see in the screenshot. Hence, your DAX formula uses the Search formula and then checks the ordinal position of it ..if it's = 1, then it classifies as Creator...Hence, the boolean in DAX..at least that's what I understood.

The sample data I had shared had more text to it followed by the segment that I want to extract...

e.g. 

Ad date - [Creators]...

This would be say, 

20171122 Facebook Post - [Creators]

 

I changed the DAX formula to this now and now it only picks up [Students] as a segment]....No more unknowns...

Segment = 
SWITCH (
      true (),  
 ISNUMBER(SEARCH ("Students", table1[Ad Set Name],1,0 )), "Students",
 ISNUMBER(SEARCH ("Creators", 'table1'[Ad Set Name],1,0 )),"Creators",
      "UNKNOWN"
)

 

Is this the right approach and if yes, how can I improve this formula to get the segments?

Anonymous
Not applicable

Hi @adilk,

 

What is the datatype of "20171122 Facebook Post - [Creators]" these records ?

 

Regards,

L.Meijdam

@Anonymous

It would be in text.

Anonymous
Not applicable

Hi @adilk,

 

That is strange I can't recreate your error, could you perhaps share a dummy pbix file ?

 

Regards,

L.Meijdam

BeemsC
Resolver III
Resolver III

Could you provide some sample data

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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