Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Trying to create a column with multiple arguments

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")))))
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Anonymous 

    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"
    )

7 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Anonymous 

    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"
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        hi Anonymous 

        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"
        )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

      hi Anonymous 

      are you creating the column in LT table?

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Yes I am