Ignore Blank Value

[zahidah_mabd]

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 Grade	Job_Grade.1	Job_Grade.2	Output	Reason
Z1	Z1		Incorrect	value 1 not in the list
P1/P2	P1	P2	Correct	both values is in the list
			Correct	blank is correct
P4/Q5	P4	Q5	Incorrect	Value 2 is not in the list
E1	E1		Correct	Value 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 ?

[zahidah_mabd]

@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]

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

[amitchandak]

@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