Logical Operators && || IN do not work

[katness]

Hi, 

I'm trying to accomplish a fairly simple Case statement in PBI lingo but the logical operators are not working at all.  Is there something with the version that I am on?  How do I fix this?

I'm on the newest version of PBI desktop, I just downloaded it bc of dashboard in a day training.  Online it says I'm on version:

Power BI

©Microsoft Corporation 2021. All rights reserved.

Service version13.0.16982.45
Client version2110.2.08110-train

Here is the IF statement that is throwing an error because it doesn't recognise the IN operator (not sure if the rest of it works since it fails on the first 'in'):

if [Counterparty] in {"RBCCM","CITIGMI"} then "EXCH"

else if [Wholesale or Retail] = "WHOLESALE" && [MANUM] <> "INTERBOOK"

&& [Risk Trade Type] <> "PHYSICALS"

&& not [Counterparty] in {"RBCCM","CITIGMI","PJM","ISONE"} then "OTC" else "OTHER"

I feel like the && and the not part is going to error as well since they are not blue.

All help is appreciated... these are fairly simple so I'm super confused as to why they wouldn't work.

[TheoC · Accepted solution]

Hi there,

I believe this is because you are using Power Query to create a Custom Column rather than using Power BI Desktop's front end to create a Calculated Column.

Note that Power Query uses the M language.  The front end of Power BI Dekstop uses DAX which incorporates logical operators, etc.

Hope this helps.

Theo 🙂

[katness]

Thank you so much!  The M language query worked and I'm going to try the logical operators mentioned in the front end to see how that goes.

[mahoneypat · Accepted solution]

The &&, || and IN syntax is for DAX and that is an M expression.  This should work instead.  You may find it frustrating/confusing, but stick with it.  It's worth it.  Note too that M is case sensitive, so the and is all lowercase.

if List.Contains({"RBCCM","CITIGMI"}, [Counterparty]) then "EXCH"

else if [Wholesale or Retail] = "WHOLESALE" and [MANUM] <> "INTERBOOK"

and [Risk Trade Type] <> "PHYSICALS"

and not List.Contains({"RBCCM","CITIGMI","PJM","ISONE"}, [Counterparty]) then "OTC" else "OTHER"

Pat

[Syndicate_Admin]

Thank you very much for your help. List.Contains was the function I was looking for.

Best regards

[TheoC · Accepted solution]

Hi there,

I believe this is because you are using Power Query to create a Custom Column rather than using Power BI Desktop's front end to create a Calculated Column.

Note that Power Query uses the M language.  The front end of Power BI Dekstop uses DAX which incorporates logical operators, etc.

Hope this helps.

Theo 🙂