Using DAX Search to find separate partial values in multiple columns

[phaering]

I am trying to create a new column that provides a simplified naming for an operating system (OS) running on a server.  I have two columns from my source data that provide the OS version and name.  I need to search those two columns, find partial values, and create the new naming for my new column.  I am able to do this if I only need to search one source column but adding in the second search has proven difficult.  I was able to figure this out in less than one minute in Tableau and I am on day 3 trying to figure this out in Power BI.  Here are the particulars (samples of my data):

ServerName	OS	OS_Full_Name	OS Best (my new value)
jerry	Win2008R2 6.1.7601	Microsoft WIndows Server 2008 R2 Enterprise x64	Win 2008 R2 Ent (this is the value I want)
elaine	Win2008 6.0.6003	Microsoft Windows Server 20008 Standard	Win 2008 Std
george	Linux Red Hat Enterprise Server 7.8 (3.10.0-1127.el7.x86_64)	Linux Red Hat Enterprise Server 7.8 (3.10.0-1127.el7.x86_64)	Red Hat 7.8
kramer	SunOS 5.10 (Generic_a47148-26)	SunOS 5.10 (Generic_a47148-26)	SunOS 5.10

For "george" and "kramer" I can run a single search line to create my new OS value (OS Best):

SEARCH("SunOS 5.10",[OS],,-1) <> -1,"SunOS 5.10"...as an example...this works just fine

But for "jerry" and "elaine" I need to search both the "OS" and the "OS_Full_Name" columns to create the new value for "OS Best" because the "OS" column cannot tell me if the version is "standard" or "enterprise".  How do I create the 'SEARCH' command structure to look in both, find the partial values, and provide the new value for "OS Best"?  My attempts look something like this but it fails to correctly differentiate between the '2008' and '2008 R2' versions in "OS"...

SEARCH("Win2008 ",[OS],,-1) && SEARCH("Standard",[OS_Full_Name],,-1) <>-1,"Win 2008 Std"

Please don't refer me to the documenation because I have already reviewed that and cannot find anything referencing multiple searchs in the same command.

Thank you!

Paul

[Icey · Accepted solution]

Hi @phaering ,

Try this:

Column =
SWITCH (
    TRUE (),
    SEARCH ( "Win2008R2", [OS],, -1 ) <> -1                           ----------------------"<> -1" is missing in your expression.
        && SEARCH ( "Enterprise", [OS_Full_Name],, -1 ) <> -1, "Win 2008 R2 Ent",
    SEARCH ( "Win2008", [OS],, -1 ) <> -1                             ----------------------"<> -1" is missing in your expression.
        && SEARCH ( "Standard", [OS_Full_Name],, -1 ) <> -1, "Win 2008 Std",
    SEARCH ( "Linux Red Hat Enterprise Server 7.8", [OS],, -1 ) <> -1, "Red Hat 7.8",
    SEARCH ( "SunOS 5.10", [OS],, -1 ) <> -1, "SunOS 5.10"
)

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

In its statement SWITCH would like to:

SWITCH(TRUE(),

SEARCH("Win2008 RT",[OS],,-1) && SEARCH("Standard",[OS_Full_Name],,-1) <>-1,"Win 2008 RT Std",

SEARCH("Win2008 ",[OS],,-1) && SEARCH("Standard",[OS_Full_Name],,-1) <>-1,"Win 2008 Std"

[Greg_Deckler]

In your SWITCH statement you would want:

SWITCH(TRUE(),

  SEARCH("Win2008 RT",[OS],,-1) && SEARCH("Standard",[OS_Full_Name],,-1) <>-1,"Win 2008 RT Std",

  SEARCH("Win2008 ",[OS],,-1) && SEARCH("Standard",[OS_Full_Name],,-1) <>-1,"Win 2008 Std"

[phaering]

This is still not working as the "&&" needs to be both and it appears that it is finding either and applying the value incorrectly.

Here is the output I'm getting and you'll see the formula is not working quite right.  I've just done a few lines and you'll see it is applying a windows name to the Red Hat OS.  These should have been assigned the "(unknown)" value per the SWITCH formula.  It is also not applying the value correctly to the different Windows 2008 and Windows 2008R2 versions.

[Icey · Accepted solution]

Hi @phaering ,

Try this:

Column =
SWITCH (
    TRUE (),
    SEARCH ( "Win2008R2", [OS],, -1 ) <> -1                           ----------------------"<> -1" is missing in your expression.
        && SEARCH ( "Enterprise", [OS_Full_Name],, -1 ) <> -1, "Win 2008 R2 Ent",
    SEARCH ( "Win2008", [OS],, -1 ) <> -1                             ----------------------"<> -1" is missing in your expression.
        && SEARCH ( "Standard", [OS_Full_Name],, -1 ) <> -1, "Win 2008 Std",
    SEARCH ( "Linux Red Hat Enterprise Server 7.8", [OS],, -1 ) <> -1, "Red Hat 7.8",
    SEARCH ( "SunOS 5.10", [OS],, -1 ) <> -1, "SunOS 5.10"
)

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[phaering · Accepted solution]

Thank you very much!  I never thought to include the "<> -1" in both parts.  Much appreciated, this worked.