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

[adilk]

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.

[v-jiascu-msft · Accepted solution]

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"
)

Best Regards!

Dale

[adilk]

@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

[v-jiascu-msft]

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

[adilk]

@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?

[v-jiascu-msft]

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.

Best Regards!

Dale

[adilk]

@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?

[v-jiascu-msft]

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

[adilk]

@v-jiascu-msft

Got it! thanks a lot.

[Anonymous]

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")

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

[adilk]

@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]

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

[adilk]

@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]

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

[adilk]

@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]

Hi @adilk,

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

Regards,

L.Meijdam

[adilk]

@Anonymous

It would be in text.

[Anonymous]

Hi @adilk,

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

Regards,

L.Meijdam

[BeemsC]

Could you provide some sample data