Forum Discussion

zahidah_mabd's avatar
zahidah_mabd
Icon for Helper I rankHelper I
4 years ago
Solved

Does NOT CONTAINSSTRING filter is not working.

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?

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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.

1 Reply

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    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.