Forum Discussion
Using DAX Search to find separate partial values in multiple columns
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
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.
Thank you very much! I never thought to include the "<> -1" in both parts. Much appreciated, this worked.
5 Replies
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"
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.
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.
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"
