Issue with comparison Ifelse <> then x

Question & Answer
1

I am trying to create a measure which tests a certain condition in my table.

condition 1: support type = ‘Ad-Hoc Away Team Night’ or ‘Away Team Night’
condition 2: service type different from Rescue, std away team night, and nurseries
my result: if the condition 1 stands and condition 2 stands, then I would get ‘1’, otherwise ‘0’. However it is not giving me that. on the contrary, I have two cases where the condition 1 stands but condition 2 is false, i.e., service type is equal to Rescue. If it is equal to rescue, then I should get 0, but I am getting 1. I tried different things and none seems to work

here the formula:

ifelse(
({support_type} = ‘Ad-Hoc Away Team Night’
AND
(
{service_type} <> ‘Standard Parcel Away Team Night’ OR
{service_type} <> ‘Nursery Route Level 1’ OR
{service_type} <> ‘Nursery Route Level 1 - Low Emissions Vehicle’ OR
{service_type} <> ‘Nursery Route Level 2’ OR
{service_type} <> ‘Nursery Route Level 2 - Low Emissions Vehicle’ OR
{service_type} <> ‘Nursery Route Level 3’ OR
{service_type} <> ‘Nursery Route Level 3 - Low Emissions Vehicle Rescue’ OR
{service_type} <> ‘Rescue’
)
)

    OR 
           
    ({support_type} = 'Away-Team Night' 
    AND 
        (
        {service_type} <> 'Standard Parcel Away Team Night' OR 
        {service_type} <> 'Nursery Route Level 1' OR 
        {service_type} <> 'Nursery Route Level 1 - Low Emissions Vehicle' OR           
        {service_type} <> 'Nursery Route Level 2' OR 
        {service_type} <> 'Nursery Route Level 2 - Low Emissions Vehicle' OR 
        {service_type} <> 'Nursery Route Level 3' OR 
        {service_type} <> 'Nursery Route Level 3 - Low Emissions Vehicle Rescue' OR 
        {service_type} <> 'Rescue'
        )
    )

      
    ,1,0)

attached the table I have and the result. the value on the right should be 0, not 1.

ifelse_v1
ifelse_v11026×661 75.2 KB

2

Hi @Alexandre_Kniepert I think your service_type conditions need to be AND not OR. It might be easier if you rewrite it using the new notin functions, so (support_type = ‘Ad-HocAway Team Night’ AND notin(service_type, [‘Rescue’, ‘std away team night’, ‘nurseries’]) for example.

3

Hi

Briliant, I havent thoght of the AND/OR thing. I adjusted the query and now it works. Notin would definately work as well, less verbose.

thank you!

1 Like