Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
108 | |
108 | |
93 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |