Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Excluding a value from condition

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?

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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

3 Replies

  • JarroVGIT's avatar
    JarroVGIT
    Resident Rockstar
    6 years ago

    Make it an AND statement:

     

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Try something like this:

     

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

     

    PBIX attached.

     

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    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