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...
christielu's avatar
christielu
Frequent Visitor
5 years ago

Hi! I'm having the exact same problem in SSAS. I used the same statement in Excel and it works. Did you find out what's causing it? How did you end up fixing it? Thank you!

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!