Forum Discussion
Helper IUnable to run multiple text search in string to create a new column, even via Switch
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
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
Helper IAnonymous
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.
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-this/td-p/19518 --- 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?
Hi adilk,
What is the datatype of "20171122 Facebook Post - [Creators]" these records ?
Regards,
L.Meijdam