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 much to it. But I keep getting error message: The syntax for 'IN' is incorrect. 

 

Can't figure out why? Do any of you see anything wrong with it?

 

Thanks.

10 Replies

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    qaiserjaved 

     

    Not sure why.. It gave me correct results. Attach the PBIX file to test with mockup data.

     

     

    You could do the same with Custom column in Query editor..

    = if [Unit Type] = "1PAT" or [Unit Type] = "1BF" then "Frontline Patrol" else "Other Support"

     

    If it helps, mark it as a solution

    Kudos are nice too

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      I am not sure but for date dax many people got syntax error that is because of extra spaces. And seperator like comma semicolans this one because of region setting.

      For Africa semicolon is comma separator.

      Just play with it and remove those blanks and give try.

      Thanks
      Pravin

      If it resolves your problem mark it as a solution and give Kudos.
      • 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.