Does NOT CONTAINSSTRING filter is not working.

[zahidah_mabd]

Hi there, I want to filter certain thing that does not contain the value given in the measure but its not working. 
This is my measure:

Sum Incorrect Exec SG = 
CALCULATE (
COUNTROWS ( 'zhpla' ),
FILTER (
'zhpla',
('zhpla'[Position Assignment Category] IN {"Permanent ~ Executive","Contract Local DH ~ Executive","Expatriate Local DH ~ Executive" })
&& (NOT (CONTAINSSTRING ( 'zhpla'[Job Grade], "P1" ))
&& NOT(CONTAINSSTRING ( 'zhpla'[Job Grade], "P2" ))
&& NOT(CONTAINSSTRING ( 'zhpla'[Job Grade], "P3" ))
&& NOT(CONTAINSSTRING ( 'zhpla'[Job Grade], "P4" ))
&& NOT(CONTAINSSTRING ( 'zhpla'[Job Grade], "P5" ))
&& NOT(CONTAINSSTRING ( 'zhpla'[Job Grade], "P6" ))
&& NOT(CONTAINSSTRING ( 'zhpla'[Job Grade], "P7" ))
&& NOT(CONTAINSSTRING ( 'zhpla'[Job Grade], "G01" ))
&& NOT(CONTAINSSTRING ( 'zhpla'[Job Grade], "G02" ))
&& NOT(CONTAINSSTRING ( 'zhpla'[Job Grade], "G03" ))
&& NOT(CONTAINSSTRING ( 'zhpla'[Job Grade], "G04" ))
&& NOT(CONTAINSSTRING ( 'zhpla'[Job Grade], "G05" ))
&& NOT(CONTAINSSTRING ( 'zhpla'[Job Grade], "G06" ))
&& NOT(CONTAINSSTRING ( 'zhpla'[Job Grade], "G07" )))
) + 0
)

  but i keep getting this error

also, is it possible to do the NOT CONTAINSSTRING part in a list using IN function? I tried that too but doesnt work.

can anyone help me?

[v-xiaotang · Accepted solution]

Hi @zahidah_mabd 

Thanks for reaching out to us.

You can try this, 

Sum Incorrect Exec SG test = 
CALCULATE (
    COUNTROWS ( 'zhpla' ),
    FILTER (
        'zhpla',
        ( 'zhpla'[Position Assignment Category]
            IN {
            "Permanent ~ Executive",
            "Contract Local DH ~ Executive",
            "Expatriate Local DH ~ Executive"
        } )
            && (
                NOT ( CONTAINSSTRING ( 'zhpla'[Job Grade], "P1" ) )
                    && NOT ( CONTAINSSTRING ( 'zhpla'[Job Grade], "P2" ) )
                        && NOT ( CONTAINSSTRING ( 'zhpla'[Job Grade], "P3" ) )
                            && NOT ( CONTAINSSTRING ( 'zhpla'[Job Grade], "P4" ) )
                                && NOT ( CONTAINSSTRING ( 'zhpla'[Job Grade], "P5" ) )
                                    && NOT ( CONTAINSSTRING ( 'zhpla'[Job Grade], "P6" ) )
                                        && NOT ( CONTAINSSTRING ( 'zhpla'[Job Grade], "P7" ) )
                                            && NOT ( CONTAINSSTRING ( 'zhpla'[Job Grade], "G01" ) )
                                                && NOT ( CONTAINSSTRING ( 'zhpla'[Job Grade], "G02" ) )
                                                    && NOT ( CONTAINSSTRING ( 'zhpla'[Job Grade], "G03" ) )
                                                        && NOT ( CONTAINSSTRING ( 'zhpla'[Job Grade], "G04" ) )
                                                            && NOT ( CONTAINSSTRING ( 'zhpla'[Job Grade], "G05" ) )
                                                                && NOT ( CONTAINSSTRING ( 'zhpla'[Job Grade], "G06" ) )
                                                                    && NOT ( CONTAINSSTRING ( 'zhpla'[Job Grade], "G07" ) )
            )
    ) 
)+0

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.