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

Thanks for the suggestions. I need to use IN operator becuase I have a large number of different comparisons. The full expression looks like this:

 

IF (
    'Unit Information'[Unit Type] IN { "1PAT","1BF" },
    "Frontline Patrol",
    IF (  'Unit Information'[Unit Type]="1SGT",
             "Frontline Admin",
        IF (
            'Unit Information'[Unit Type] IN { "2MAR","2DIVE","2EDU","2K9","2MCB","2OPS","2PSU","2RBU","2TAC","2TMED","3COAST","4MOTO","4RIDE","4RTRF","4SAP","5YED" },
            "Regional Support", "Other Support"
        )
    )
)

 

I shortened it to validate the syntax. 

 

I'm using it inside an SSAS tabular model. When I render the object in Power BI Desktop, it gives the error.

 

I also tried the SWITCH function. But problem with SWITCH is that it only allows a max of 10 values when used with a linked server. This is not DAX limitation but SQL Server linked server limitation. 

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