Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
katness
Regular Visitor

Logical Operators && || IN do not work

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

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"

 

error.PNG

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.

2 ACCEPTED SOLUTIONS
TheoC
Super User
Super User

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 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
katness
Regular Visitor

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
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

Best regards

TheoC
Super User
Super User

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 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.