How to Pick The Correct Coding in Col B?

[desmond_lee]

Hi There

I would like an expression that pick the coding column value correctly. The condition is as per the table. For example [ETHINIC GROUP] =CHINESE, INDIAN, MALAY, OTHER [TYPE]=STAFF will pick CODING value what start with "S" and in this example is S753. And also CONTRACT and INTERN need only one condition.

I use this and it partially work

ANY(
SELECT(
CODING EMPLOYEE ID[CODING],
OR(
AND(
IN([_THISROW].[Ethnic Group], LIST("CHINESE", "INDIAN", "MALAY", "OTHERS")),
[_THISROW].[Type] = "STAFF"
),
AND(
IN([_THISROW].[Ethnic Group], LIST("CHINESE", "INDIAN", "MALAY", "OTHERS")),
[_THISROW].[Type] = "LOCAL"
),
AND(
IN([_THISROW].[Ethnic Group], LIST("MYANMAR", "NEPAL", "PHILIPPINES")),
[_THISROW].[Type] = "FOREIGNER"
)
)
)
)

[WillowMobileSys · Accepted solution]

I think you are confusing yourself.  Your expression should be simply like this:

ANY(
    SELECT(CODING EMPLOYEE ID[CODING], 
           AND(IN([_THISROW].[Ethnic Group], [Ethnic Group]),
               [_THISROW].[Type] = [Type]
           )
    )
)

This should work for INTERN and CONTRACT as well since the Type column will be blank.

I hope this helps!