CONTAINS function (DAX)

[Anonymous]

I created a column that shows if an 'agent' in a device is broken. However, I want to exclude devices where device name contains "ESX" or "Appliance" or the Operating System contains "vSphere" or "Hpux" or "solaris" from the logic I will apply for broken agents. I used a NOT (CONTAINS(...)) function to do this (in orange below). Is this accurate?

The highlighted portion (in orange) below:

IF(AND('Server&VDI'[Data Source]="Falcon",NOT(CONTAINS('Server&VDI','Server&VDI'[Operating system],"Solaris",'Server&VDI'[Operating system],"vSphere",'Server&VDI'[Operating system],"HPUX",'Server&VDI'[Device Name],"Appliance",'Server&VDI'[Device Name],"ESX"))),IF(AND(DATEDIFF('Server&VDI'[Falcon Scan Date ],'Server&VDI'[Maximum],DAY)>=15,DATEDIFF('Server&VDI'[Maximum],[LatestDate],DAY)<=60),"Broken Agent"))

[Anonymous · Accepted solution]

Hi @Anonymous,

You can use DAX to achieve it with the help of function FIND (case-sensitive)or SEARCH (case-insensitive)as below screenshot:

GoodorBrokenAgent = if(SEARCH("ESX",'Server&VDI'[Device Name],1,0)>0||SEARCH("Appliance",'Server&VDI'[Device Name],1,0)>0

||SEARCH("Solaris",'Server&VDI'[Operating system],1,0)>0||SEARCH("vSphere",'Server&VDI'[Operating system],1,0)>0

||SEARCH("vSphere",'Server&VDI'[Operating system],1,0)>0,"Broken Agent","Good Agent")

Best Regards

Rena

[amitchandak]

As per doc. Contains means all values

https://docs.microsoft.com/en-us/dax/contains-function-dax

You have use search in dax or  https://docs.microsoft.com/en-us/powerquery-m/list-containsany in M

[Greg_Deckler]

This seems eerily similar to another question that was asked on this site a few days ago. In answer to your current question I believe the answer is "No, you cannot use CONTAINS to do what you want to do". The reason is that CONTAINS searches for the specified text within the entire value of the column, it does not do partial matches in values of columns.

[Anonymous]

Thanks Greg! What function should I use instead?