Trying to create a column with multiple arguments

[jshiv]

I'm looking to create a new column with several IF and OR arguments but there is only a max of 2 allowed.

I have seen that || can be used, but I'm unsure of where to use it. I'm relatively new to PowerBi.

Measure = IF(OR('LT'[NBR]=6002,'LT'[NBR]=7102,'LT'[NBR]=7088,'LT'[NBR]=7087),"ON1",IF(OR('LT'[NBR]=6063,'LT'[NBR]=7101,'LT'[NBR]=6081,'LT'[NBR]=6080),"AB",IF(OR('LT'[NBR]=7103,'LT'[NBR]=6097,'LT'[NBR]=6098,'LT'[NBR]=6093),"ON2",IF(OR('LT'[NBR]=6072,'LT'[NBR]=6064),"BC",IF('LT'[NBR]=6083,"SK","unknown")))))

[jshiv]

I tried it, but keep getting the following error; 

A single value for column 'NBR' in table 'LT' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[FreemanZ]

hi @jshiv 

are you creating the column in LT table?

[jshiv]

Yes I am

[FreemanZ]

hi @jshiv 

try like:

column = 
SWTICH(
    [NBR],
    6002 || 7102 || 7087, "ON1",
    6063 || 7101 || 6081 || 6080, "AB",
    7103 || 6097 || 6098 || 6093, "ON2",
    6072 || 6064,  "BC", 
    6083,  "SK", 
    "unknown"
)

[jshiv]

I tried inputting a New Column instead of a New Measure and get the following error:

Function 'SWITCH' does not support comparing values of type Integer with values of type True/False. Consider using the VALUE or FORMAT function to convert one of the values.

[FreemanZ · Accepted solution]

hi @jshiv 

aha, try like:

column = 

SWTICH(

    TRUE(),

    [NBR] IN {6002, 7102, 7087}, "ON1",

    [NBR] IN {6063, 7101, 6081, 6080}, "AB",

    [NBR] IN {7103, 6097, 6098, 6093}, "ON2",

    [NBR] IN {6072, 6064}, "BC", 

    [NBR]=6083, "SK", 

    "unknown"

)

[jshiv]

Perfect, thank you!!!