Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

CONTAINS function (DAX)

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"))
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Greg! What function should I use instead?

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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