Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
Solved! Go to 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
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
@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
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.
Best Regards!
Dale
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
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
@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
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.
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?
Hi @adilk,
What is the datatype of "20171122 Facebook Post - [Creators]" these records ?
Regards,
L.Meijdam
@Anonymous
It would be in text.
Hi @adilk,
That is strange I can't recreate your error, could you perhaps share a dummy pbix file ?
Regards,
L.Meijdam
Could you provide some sample data
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
126 | |
110 | |
87 | |
70 | |
66 |