Forum Discussion

zahidah_mabd's avatar
zahidah_mabd
Helper I
4 years ago

Ignore Blank Value

Hi I have a column [Job Grade] in which I separated the value by delimeter "/" into two new columns [Job_Grade.1] and [Job_Grade.2]. 

The example data is below:

Job GradeJob_Grade.1Job_Grade.2OutputReason
Z1Z1 Incorrectvalue 1 not in the list
P1/P2P1P2Correctboth values is in the list
   Correctblank is correct
P4/Q5P4Q5IncorrectValue 2 is not in the list
E1E1 CorrectValue 1 is in the list

 


The [Job Grade] should be within the values here: 
Job Grade = {"P1", "P2", "P3", "P5", "P6", "P7","A1", "A2","A3", "D1","D2","D3","E1","E2","E3","E4"}
Blank( ) values in the Job Grade is also considered correct.

I want to find the Incorrect Job Grade values (values not equal to any of the above list). Since there is a slash, I decided to separate the values into 2 columns as mentioned earlier.

So I created this measure. I works except for the fact that it considers Blank values in [Job_Grade.2] is wrong. If there is only 1 value in the main column [Job Grade] then only [Job_Grade.1] will have a value while [Job_Grade.2] will be left empty. So in this case, the measure should only cosider [Job_Grade.1] to decided if its false.

TEST = 

CALCULATE( COUNTROWS('zhpla'),

FILTER(

'zhpla',

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

'zhpla'[Position Assignment Category] = "Int. Secondee PET ~ Executive")

&& ('zhpla'[Job_Grade.1] <> BLANK()) &&

(NOT 'zhpla'[Job_Grade.1] IN {"P1", "P2", "P3", "P5", "P6", "P7",

"A1", "A2","A3", "D1","D2","D3","E1","E2","E3","E4"} )

 &&

(NOT 'zhpla'[Job_Grade.2] IN {"P1", "P2", "P3", "P5", "P6", "P7",

"A1", "A2","A3", "D1","D2","D3","E1","E2","E3","E4", BLANK()} )

)

)+0

The [Output] and [Reason] columns are to describe the output that i want. How do I ignore the blank values? Im not sure how because right now it gives me incorrect if the 2nd value is blank eventho value 1 is correct.
Can u help me ?

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    zahidah_mabd , try if this can work

     

    TEST =

    CALCULATE( COUNTROWS('zhpla'),

    FILTER(

    'zhpla',

    ('zhpla'[Position Assignment Category] = "Permanent ~ Executive" ||
    'zhpla'[Position Assignment Category] = "Int. Secondee PET ~ Executive")

    && (not(Blank('zhpla'[Job_Grade.1])) &&

    (NOT 'zhpla'[Job_Grade.1] IN {"P1", "P2", "P3", "P5", "P6", "P7",

    "A1", "A2","A3", "D1","D2","D3","E1","E2","E3","E4"} )

    && (not(Blank('zhpla'[Job_Grade.2])) &&

    (NOT 'zhpla'[Job_Grade.2] IN {"P1", "P2", "P3", "P5", "P6", "P7",

    "A1", "A2","A3", "D1","D2","D3","E1","E2","E3","E4"} )

    )

    )+0

  • zahidah_mabd's avatar
    zahidah_mabd
    Helper I
    4 years ago

    amitchandak 
    No it still doesnt work,,, it still consider empty value 2 as incorrect.
    Also, I got an error when using  (not(Blank('zhpla'[Job_Grade.1]))  so i use ISBLANK instead .. like  (not(ISBLANK('zhpla'[Job_Grade.1])) but it still doesnt work.

    Is it possible to to an IF condition in this case? im not sure how tho. 

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

      Hi, zahidah_mabd 

      I think you could try treating the blank value as an element in the list as well.

      Output = 
var _jobgrade={"P1", "P2", "P3", "P5", "P6", "P7","A1", "A2","A3", "D1","D2","D3","E1","E2","E3","E4",""}
RETURN
    IF (
        MAX ( zhpla[Job_Grade.1] ) IN _jobgrade
           && MAX ( zhpla[Job_Grade.2] ) IN _jobgrade,
        "Correct",
        "Incorrect"
    )

      Best Regards,
      Community Support Team _ Eason