Forum Discussion

zahidah_mabd's avatar
zahidah_mabd
Helper I
4 years ago
Solved

NOT CONTAINS Measure not working

Hi, I want to filter value NOT CONTAINS the specified value but its not working

 

Mismatch Executive = 
CALCULATE( COUNTROWS('table'),
FILTER(
'table',
('table'[Position Assignment Category] = "Permanent ~ Executive" ||
'table'[Position Assignment Category] = "Int. Secondee PET ~ Executive")
&& ('table'[Job Grade] <> BLANK()) && 
( NOT ( 
CONTAINS ( 'table','table'[Job Grade], "P1" )
|| CONTAINS ( 'table','table'[Job Grade], "P2" )
|| CONTAINS ( 'table','table'[Job Grade], "P3" )

))
)
)+0

can you help me?

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, zahidah_mabd 

    Please try formula as below:

     

    Mismatch Executive =
CALCULATE (
    COUNTROWS ( 'table' ),
    FILTER (
        'table',
        ( 'table'[Position Assignment Category] = "Permanent ~ Executive"
            || 'table'[Position Assignment Category] = "Int. Secondee PET ~ Executive" )
            && ( 'table'[Job Grade] <> BLANK () )
            && ( NOT ( 'table'[Job Grade] IN { "P1", "P2", "P3" } ) )
    )
) + 0

     

    Best Regards,
    Community Support Team _ Eason

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    zahidah_mabd , Try like

     

    Mismatch Executive =
    CALCULATE( COUNTROWS('table'),
    FILTER(
    'table',
    ('table'[Position Assignment Category] = "Permanent ~ Executive" ||
    'table'[Position Assignment Category] = "Int. Secondee PET ~ Executive")
    && not(Isblank('table'[Job Grade] )) &&
    ( NOT (
    CONTAINSSTRING ( 'table','table'[Job Grade], "P1" )
    || CONTAINSSTRING ( 'table','table'[Job Grade], "P2" )
    || CONTAINSSTRING ( 'table','table'[Job Grade], "P3" )

    ))
    )
    )+0

    • v-easonf-msft's avatar
      v-easonf-msft
      Community Support
      4 years ago

      Hi, zahidah_mabd 

      Please try formula as below:

       

      Mismatch Executive =
CALCULATE (
    COUNTROWS ( 'table' ),
    FILTER (
        'table',
        ( 'table'[Position Assignment Category] = "Permanent ~ Executive"
            || 'table'[Position Assignment Category] = "Int. Secondee PET ~ Executive" )
            && ( 'table'[Job Grade] <> BLANK () )
            && ( NOT ( 'table'[Job Grade] IN { "P1", "P2", "P3" } ) )
    )
) + 0

       

      Best Regards,
      Community Support Team _ Eason