Forum Discussion

qaiserjaved's avatar
qaiserjaved
Frequent Visitor
6 years ago

The syntax for 'IN' is incorrect error

I have a simple calculated column. The definition looks like this   [Unit Category] := IF ( 'Unit Information'[Unit Type] IN { "1PAT", "1BF" }, "Frontline Patrol", "Other Support" )   Nothing mu...
AntrikshSharma's avatar
AntrikshSharma
Icon for Community Champion rankCommunity Champion
5 years ago

christielu PBI and Excel has it since late 2016 but SSAS has it from SQL server 2016 onwards, which version of SSAS are you using?

christielu's avatar
christielu
Frequent Visitor
5 years ago

Thank you Antriksh! Well that's my problem! I'm using 2015... Is there a workaround? 

  • qaiserjaved's avatar
    qaiserjaved
    Frequent Visitor
    5 years ago

    I ended up using this format instead.

     

    IF (
      'Unit Information'[Unit Type] = "1PAT" || 'Unit Information'[Unit Type] = "1BF",
      "Frontline Patrol",
        IF ( 'Unit Information'[Unit Type]="1SGT",
          "Frontline Admin",
          IF (
            'Unit Information'[Unit Type] = "2MAR" || 'Unit Information'[Unit Type] = "2DIVE" || 'Unit Information'[Unit Type] = "2EDU" || 'Unit Information'[Unit Type] = "2K9" || 'Unit Information'[Unit Type] = "2MCB" || 'Unit Information'[Unit Type] = "2OPS" || 'Unit Information'[Unit Type] = "2PSU" || 'Unit Information'[Unit Type] = "2RBU" || 'Unit Information'[Unit Type] = "2TAC" || 'Unit Information'[Unit Type] = "2TMED" || 'Unit Information'[Unit Type] = "3COAST" || 'Unit Information'[Unit Type] = "4MOTO" || 'Unit Information'[Unit Type] = "4RIDE" || 'Unit Information'[Unit Type] = "4RTRF" || 'Unit Information'[Unit Type] = "4SAP" || 'Unit Information'[Unit Type] = "5YED",
            "Regional Support",
            IF (
    'Unit Information'[Unit Type] = "3BEAT" || 'Unit Information'[Unit Type] = "3BIKE" || 'Unit Information'[Unit Type] = "3CR" || 'Unit Information'[Unit Type] = "3CS" || 'Unit Information'[Unit Type] = "3NPU" || 'Unit Information'[Unit Type] = "3SE" || 'Unit Information'[Unit Type] = "4DTRF" || 'Unit Information'[Unit Type] = "4DTU" || 'Unit Information'[Unit Type] = "4SGT" || 'Unit Information'[Unit Type] = "5CIB",
              "Divisional Support",
              IF (
    'Unit Information'[Unit Type] = "2STR" || 'Unit Information'[Unit Type] = "2ROB" || 'Unit Information'[Unit Type] = "5AUTO" || 'Unit Information'[Unit Type] = "5CAR" || 'Unit Information'[Unit Type] = "5DRUG" || 'Unit Information'[Unit Type] = "5FRAUD" || 'Unit Information'[Unit Type] = "5IDENT" || 'Unit Information'[Unit Type] = "5INTEL" || 'Unit Information'[Unit Type] = "5SVU",
              "Investigative Support",
              IF (
    'Unit Information'[Unit Type] = "1DI" || 'Unit Information'[Unit Type] = "6CRTS" || 'Unit Information'[Unit Type] = "6CUR" || 'Unit Information'[Unit Type] = "6PE" || 'Unit Information'[Unit Type] = "6SUMM" || 'Unit Information'[Unit Type] = "6TOW" || 'Unit Information'[Unit Type] = "7ADM" || 'Unit Information'[Unit Type] = "7SENR" || 'Unit Information'[Unit Type] = "99GPS",
              "Other Support", "Other Support"
              )
            )
          )
        )
      )
    )

    • christielu's avatar
      christielu
      Frequent Visitor
      5 years ago

      Thank you! Unfortunately that is what I would have had to do using SSAS 2015. I ended up adding the column in the SQL table, which IN operator is not an issue!