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...
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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Could you try to remove blank if blanks are there or add blanks if blanks are not there.

    Also check regional seperator setting on your system.

    And make sure you are using latest version of power bi desktop and server version.

    Thanks,
    Pravin
  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    qaiserjaved 

     

    If you want to use IN inside power query, refer to this post..

     

    https://community.powerbi.com/t5/Desktop/IN-OPERATOR-in-PowerBi/td-p/174285

     

    If 10 elements is the limitation in SWITCH, split the case and use the same value for all the cases..

     

    For example.

    SWITCH (
TRUE(),
TEST IN {A,B,C,D),"TRUE",
TEST IN {E,F,G,H},"TRUE",
"FALSE"
)

     

    If it helps, mark it as a solution

    Kudos are nice too

  • 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
      Community 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?