Forum Discussion
Create Column
- 6 years ago
Well, that's frankly a horrible construction, doesn't Tableau have a SWITCH/CASE statement?
Use a SWITCH(TRUE()...) statement. Replace CONTAINS with SEARCH or FIND like:
Column?
SWITCH(TRUE(),
SEARCH("AIX 5.3",[OS],,-1 ) <> -1 THEN "AIX 5.3",
SEARCH("AIX 6.1",[OS],,-1 ) <> -1 THEN "AIX 6.1",
...
)
Well, that's frankly a horrible construction, doesn't Tableau have a SWITCH/CASE statement?
Use a SWITCH(TRUE()...) statement. Replace CONTAINS with SEARCH or FIND like:
Column?
SWITCH(TRUE(),
SEARCH("AIX 5.3",[OS],,-1 ) <> -1 THEN "AIX 5.3",
SEARCH("AIX 6.1",[OS],,-1 ) <> -1 THEN "AIX 6.1",
...
)
Thank you! What do the -1 values represent in this formula (How do I read that full formula in words)? Also, how do I include the AND aspect if I need to search/compare values in two separate columns to create my new value?
- Greg_Deckler6 years agoCommunity Champion
Sure, SEARCH and all other DAX functions are defined here:
https://docs.microsoft.com/en-us/dax/search-function-dax
Also, intellisense will tell you what is what when typing DAX formulas. But in SEARCH's case, first see my second post because you don't need the -1 I believe, but SEARCH has the format:
SEARCH( <search text>, <within text>, <start position>, <alternate return value if not found> )
So, let's tackle the second part, the SWITCH(TRUE()...) construct allows you to have any DAX that returns a logical value on the left side, so you read the SWITCH like:
SWITCH(
TRUE(),
<logical condition 1>, <return value 1 if logical condition 1 is true>,
<logical condition 2>, <return value 2 if logical condition 2 is true>,
... (as many of these as you want)
<default return value optional>
)
Now, logical conditions can include && and || for AND and OR so:
SWITCH(TRUE(),
SEARCH("AIX 5.1") || SEARCH("AIX 6.3") || SEARCH("AIX 99.83"),"AIX",
for example. Or any combination of logic. You can use parens like:
( this || that) && this other thing
for example.
- phaering6 years agoHelper I
I'm still having problems with instances where I need to search for values in two separate columns to create the value in my new column. This formula works great for single column searches:
SEARCH("Linux Red Hat Enterprise Server 7.8",VW_LCM_BF_SERVERS[OS],,-1) <> -1,"Red Hat 7.8",But when I need to also search another column to ensure I'm providing the correct value I am not getting the result I need. I am using this formula:
SEARCH("Win2008 ",VW_LCM_BF_SERVERS[OS],,-1) && SEARCH("Standard",VW_LCM_BF_SERVERS[OS_FULL_NAME],,-1) <> -1,"Win 2008 Std",It is putting my result in rows where the 'OS' is not absolutely equal to "Win2008 " (that is a deliberate space after the 8). For example, the 'OS' is "Win2008R2". How do I write the formula to only put the desired result in the rows where both search values are met exactly?Thank you!