A table of multiple values was supplied where a single value was expected.

[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.

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 Incorrect.

ESG Exec = 

Var JG1_Exec = {"P1", "P2", "P3", "P5", "P6", "P7", "A1", "A2","A3", "D1","D2","D3","E1","E2","E3","E4"}

Var JG2_Exec = {"P1", "P2", "P3", "P5", "P6", "P7", "A1", "A2","A3", "D1","D2","D3","E1","E2","E3","E4"}

RETURN

Switch(true(),

EXACT('zhpla'[Job_Grade.1], JG1_Exec) = FALSE() && EXACT('zhpla'[Job_Grade.2], JG2_Exec) = FALSE(), "F/F",

EXACT('zhpla'[Job_Grade.1], JG1_Exec) = FALSE() && 'zhpla'[Job_Grade.2] = BLANK(), "F/T",

EXACT('zhpla'[Job_Grade.1], JG1_Exec) = FALSE() || EXACT('zhpla'[Job_Grade.2], JG2_Exec) = FALSE(), "F/F",

"T") 

but im getting this error

A table of multiple values was supplied where a single value was expected.

The [Output] and [Reason] columns are to describe the output that i want. Im not sure how because right now it gives me incorrect if the 2nd value is blank eventho value 1 is correct.
Can anyone help me ? or can give me a different measure?

[Anonymous]

@zahidah_mabd , you can create a measure called JobGrade1Valid with this DAX:

JobGrade1Valid = 
    VAR vJobGrade = SELECTEDVALUE('YourTable'[Job_Grade.1])
    VAR vJobGradeValidCount = 
        SUMX(
            'ValidJobGrades'
            ,IF(EXACT('ValidJobGrades'[ValidJobGrade], vJobGrade), 1, 0)
        )
    RETURN
        IF(vJobGradeValidCount = 0, "Invalid", "Valid")

You will also need to create a table (called ValidJobGrades in ther measure above) and populate it with a row for all the valid grades, including a row with a blank JobGrade. With your sample data above and I've added two rows with invalid JobGrades d2 and p1. My measure gives me these results:

Having the table of valid grades is more flexible, because you can add or remove grades without having to change the measure.

The statement "EXACT('zhpla'[Job_Grade.1], JG1_Exec)" in your original measure wasn't working because EXACT can only take a a single text value for each parameter; the JG1_Exec is a table of values, so it produced an error. To achieve the goal, I used a SUMX to iterate through each possible valid JobCode and compare it with EXACT to the currenly selected row in the main table.

Hope this helps.