Excluding a value from condition

[Anonymous]

I am trying to filter out records where the column Device_Name contains = "ESX" or "Appliance". I have this query:

IF(NOT(CONTAINS(TableName,Device_Name,"ESX||"Appliance")))

The problem is there are values under Device_Name such as Labesx which I don't want filtered out. I just want to filter out device names that just contain the word ESX separately such as (esx-111, esx-222, esxI, rather than being a part of a string such as labesx.

How do I accomplish this?

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i advise using LEFT function.

I create a simple sample to test:

Then create a column to extract specified string like 'esx' or 'Appliance':

Column = IF(LEFT('Table'[Device_Name],3)="esx"||LEFT('Table'[Device_Name],9)="Appliance",1,0)

Apply a filter about this column to the visual, the result shows:

Hope this helps.

Best Regards,

Giotto Zhi

[Greg_Deckler]

Try something like this:

Table 2 = 
  FILTER(
      'Table',
      SEARCH("ESX",[Name],1,-1) <> 1 &&
        SEARCH("Appliance",[Name],1,-1) <> 1
  )

PBIX attached.

[JarroVGIT]

Make it an AND statement:

IF(NOT(CONTAINS(TableName,Device_Name,"ESX")) && NOT(CONTAINS(TableName,Device_Name,"Appliance")), <value for true>, <value for false>)