Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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.
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!
@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?
Thank you Antriksh! Well that's my problem! I'm using 2015... Is there a workaround?
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"
)
)
)
)
)
)
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |