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
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:
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.
Solved! Go to 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 🙂
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you very much for your help. List.Contains was the function I was looking for.
Best regards
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
109 | |
106 | |
92 | |
68 |
User | Count |
---|---|
167 | |
130 | |
129 | |
95 | |
91 |