Forum Discussion

zahidah_mabd's avatar
zahidah_mabd
Helper I
4 years ago
Solved

Filter Not Equal To Text Not Working

Hi,  I want to create a measure that filters out certain values from a few columns.

 

[Employee Category] is equal to "Contract - TTS" and "Permanent -TTS"

[Job Grade] is not equal to "TTS1" and "TTS2" (meaning that only TTS1 and TTS2 are valid grades, The rest is considered Incorrect)

 

So I want to create a measure to count the total data that has the incorrect salary grade for the employee category.

This is my measure


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

But unfortunately, it doesnt filter the not equal to part correctly. Both columns are text type.

Employee CategoryJob GradeMatch
Permanent - TTSTTS1Correct
Contract - TTSTTS2Correct
Contract - TTS Incorrect
Permanent - TTSFG3Incorrect
Permanent - ABCQR2Correct
Contract - CDRTTS2Incorrect

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) 

Can anyone help me?

Need Help
  • rajulshah's avatar
    rajulshah
    4 years ago

    Hello zahidah_mabd ,

     

    Please try the following measure:

    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 based on the measure you provided, but if we go by the conditions you provided above your formula, the DAX query provided is incorrect.
    Please validate.

4 Replies

  • rajulshah's avatar
    rajulshah
    Resident Rockstar
    4 years ago

    Hello zahidah_mabd ,

     

    Please try the following measure:

    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 based on the measure you provided, but if we go by the conditions you provided above your formula, the DAX query provided is incorrect.
    Please validate.

    • zahidah_mabd's avatar
      zahidah_mabd
      Helper I
      4 years ago

      Yay, it worked like a charm! Thank youuuu so much rajulshah 

      Also, can you explain a bit about when u mentioned the DAX Query is incorrect. Im still new to power BI

      • rajulshah's avatar
        rajulshah
        Resident Rockstar
        4 years ago

        My pleasure zahidah_mabd .

         

        When I saw the latest description, it was edited and it now makes sense for the formula you used. For the earlier description, I thought it was not correct.

        Anyway, glad that it worked out.

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi zahidah_mabd 
    Please try

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