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. 

VasTg's avatar
VasTg
Icon for Memorable Member rankMemorable 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