Filter value or some part of the value in the column

[zahidah_mabd]

Hi, I want to filter out the text value from specific columns but sometimes the user might add a bracket in between the 2 accepted value for [Job Grade] which is "TTS1" and "TTS2".

The accepted value should be "TTS1" or "TTS2" but in some cases "TTS1/TTS2" is also accepted. 

So I want to filter value that contains "TTS1" or "TTS2". 

Im not sure if I can use CONTAINSSTRING for this or if there is a way to filter certain value from the text.

this is my formula but it only detects specifically TTS1 and TTS2

Sum Mismatch TTS Job Grade = 
CALCULATE( COUNTROWS('table),
FILTER(
'table',
('table'[Employee Category] = "Permanent - TTS" ||
'table'[Employee Category] = "Contract - TTS") &&
('table'[Job Grade] <> "TTS1" &&
'table'[Job Grade] <> "TTS2" )
)
)+0

This is an example of data, the bold column does not exist but only to illustrate the matching for category and job grade. What I want is to count the number of Incorrect value specifically for TTS (contract and Permanent) 

Employee Category	Job Grade	Match
Permanent - TTS	TTS1	Correct
Contract - TTS	TTS2	Correct
Contract - TTS		Incorrect
Permanent - TTS	FG3	Incorrect
Permanent - ABC	QR2	Correct
Contract - CDR	TTS1/TTS2	Correct

[VahidDM · Accepted solution]

Hi @zahidah_mabd 

Try this measure:

Sum Mismatch TTS Job Grade =
COUNTROWS (
    FILTER (
        'Table',
        CONTAINSSTRING ( 'Table'[Job Grade], "TTS1" )
            || CONTAINSSTRING ( 'Table'[Job Grade], "TTS2" )
    )
)

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[zahidah_mabd]

Thank you @VahidDM . The measure worked but since I want to find the mismatching values (Incorrect), I added the NOT function before the CONTAINSSTRING and now I can get the results that I want. I forgot to mention that I want to extract the incorrect values. But nevertheless, Thank youuu so much for your help

[VahidDM]

Glad that helped, and you found the solution.

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[zahidah_mabd]

Hi, @VahidDM 

Do you have a better way to get the Incorrect result? 
meaning I want to get the data that doesnt contains the specified value. 

Sum Incorrect Exec SG = 

CALCULATE( COUNTROWS('zhpla'),

FILTER(

'zhpla',

('zhpla'[Position Assignment Category] = "Permanent ~ Executive" ||

'zhpla'[Position Assignment Category] = "Contract Local DH ~ Executive" ||

'zhpla'[Position Assignment Category] = "Expatriate Local DH ~ Executive") &&

NOT ( 

CONTAINSSTRING ( 'zhpla'[Job Grade], "P1" ) || 

CONTAINSSTRING ( 'zhpla'[Job Grade], "P2" ) ||

CONTAINSSTRING ( 'zhpla'[Job Grade], "P3" ) ||

CONTAINSSTRING ( 'zhpla'[Job Grade], "P4" ) ||

CONTAINSSTRING ( 'zhpla'[Job Grade], "P5" ) ||

CONTAINSSTRING ( 'zhpla'[Job Grade], "P6" ) ||

CONTAINSSTRING ( 'zhpla'[Job Grade], "P7" ) ||

CONTAINSSTRING ( 'zhpla'[Job Grade], "G01" ) ||

CONTAINSSTRING ( 'zhpla'[Job Grade], "G02" ) ||

CONTAINSSTRING ( 'zhpla'[Job Grade], "G03" ) ||

CONTAINSSTRING ( 'zhpla'[Job Grade], "G04" ) ||

CONTAINSSTRING ( 'zhpla'[Job Grade], "G05" ) ||

CONTAINSSTRING ( 'zhpla'[Job Grade], "G06" ) ||

CONTAINSSTRING ( 'zhpla'[Job Grade], "G07" ) )

)+0)

 I tried this formula by adding NOT and it worked but if I have more values in the filter than it kinda dont work anymore... I get an error

A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
 

[VahidDM]

@zahidah_mabd 

Try this:

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" )
                        || CONTAINSSTRING ( 'zhpla'[Job Grade], "P2" )
                        || CONTAINSSTRING ( 'zhpla'[Job Grade], "P3" )
                        || CONTAINSSTRING ( 'zhpla'[Job Grade], "P4" )
                        || CONTAINSSTRING ( 'zhpla'[Job Grade], "P5" )
                        || CONTAINSSTRING ( 'zhpla'[Job Grade], "P6" )
                        || CONTAINSSTRING ( 'zhpla'[Job Grade], "P7" )
                        || CONTAINSSTRING ( 'zhpla'[Job Grade], "G01" )
                        || CONTAINSSTRING ( 'zhpla'[Job Grade], "G02" )
                        || CONTAINSSTRING ( 'zhpla'[Job Grade], "G03" )
                        || CONTAINSSTRING ( 'zhpla'[Job Grade], "G04" )
                        || CONTAINSSTRING ( 'zhpla'[Job Grade], "G05" )
                        || CONTAINSSTRING ( 'zhpla'[Job Grade], "G06" )
                        || CONTAINSSTRING ( 'zhpla'[Job Grade], "G07" )
                )
    ) + 0
)

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[zahidah_mabd]

nope, it doesnt work. still getting the same error.