Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
phaering
Helper I
Helper I

Create Column

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!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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",

...

)


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

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",

...

)


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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!

 

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",

...

)


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.