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
How do I create a new column that uses a formula to generate a new value based on partial values from two other columns? I was able to do this very easily in Tableau using a lengthy IF(CONTAINS)/THEN and ELSEIF(CONTAINS)/THEN formula. What I’m trying to do is create a new, normalized (simplified) name for the operating system value from my source file. I have a long list of servers and their associated OS value. The value is from a polling agent which can provide multiple variations for the exact same OS. Here is a snippet from my Tableau script that works great:
//AIX
IF CONTAINS([OS], "AIX 5.3") THEN "AIX 5.3"
ELSEIF CONTAINS([OS], "AIX 6.1") THEN "AIX 6.1"
ELSEIF CONTAINS([OS], "AIX 7.1") THEN "AIX 7.1"
ELSEIF CONTAINS([OS], "AIX 7.2") THEN "AIX 7.2"
ELSEIF CONTAINS([OS], "AIX VIOS 6.1") THEN "AIX 6.1"
ELSEIF CONTAINS([OS], "AIX VIOS 7.2") THEN "AIX 7.2"
//Windows
ELSEIF CONTAINS([OS], "Win10") AND CONTAINS([OS - Full Name], "Pro") THEN "Win 10 Pro"
ELSEIF CONTAINS([OS], "Win10") AND CONTAINS([OS - Full Name], "Ent") THEN "Win 10 Ent"
ELSEIF CONTAINS([OS], "Win10") THEN "Win 10"
ELSEIF CONTAINS([OS], "Win2000") THEN "Win 2000"
ELSEIF CONTAINS([OS], "Win2003") AND CONTAINS([OS - Full Name], "Data") THEN "Win 2003 DC"
ELSEIF CONTAINS([OS], "Win2003") AND CONTAINS([OS - Full Name], "Standard") THEN "Win 2003 Std"
ELSEIF CONTAINS([OS], "Win2003") AND CONTAINS([OS - Full Name], "Enterprise") THEN "Win 2003 Ent"
ELSEIF CONTAINS([OS], "Win2003") AND CONTAINS([OS - Full Name], "Web") THEN "Win 2003 Web"
ELSEIF CONTAINS([OS], "Win2003") THEN "Win 2003 Std"
ELSE “(unknown)”
END
I would call this new column “OS Best”
Thank you!
Solved! Go to Solution.
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?
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.
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:
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:
They probably don't need the <> -1, only SEARCH statements should do because they return blank if not found to be considered FALSE so, save yourself some writing. So
Column?
SWITCH(TRUE(),
SEARCH("AIX 5.3",[OS]) THEN "AIX 5.3",
SEARCH("AIX 6.1",[OS]) THEN "AIX 6.1",
...
)
How do I write a formula where I can hit <ENTER> so I can have one row per "search"? When I try to create a column it seems like I am being limited to writing the entire formula on one line. Thank you!
Use Alt-Enter
I created a quick sample of my entire script and received an error message: "Function 'SWITCH' does not support comparing values fo type True/False with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values."
I have no idea what this means or how to fix it.
Thank you!
Here is the view of what I have entered so far:
Column = SWITCH(TRUE(),
//AIX
SEARCH("AIX 5.3",[OS],"AIX 5.3",
SEARCH("AIX 6.1",[OS],"AIX 6.3",
SEARCH("AIX 7.1",[OS],"AIX 7.1",
SEARCH("AIX VIOS 6.1",[OS],"AIX 6.1",
//HPUX
SEARCH("HP-UX B.11.11",[OS],"HPUX 11.11",
SEARCH("HP-UX B.11.23",[OS],"HPUX 11.23",
SEARCH("HP-UX B.11.31",[OS],"HPUX 11.31",
//Linux Other
SEARCH("CentOS 4.8",[OS],"CentOS 4.8",
SEARCH("CentOS 5.8",[OS],"CentOS 5.8",
SEARCH("CentOS 6.0",[OS],"CentOS 6.0",
SEARCH("CentOS 6.10",[OS],"CentOS 6.10",
"(unknown)"
)
This is where I get the error message. Thank you!
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 |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
111 | |
59 | |
57 |