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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Conditional Column IF-AND-OR Statements Not Working as i e

i'm trying to create a complex formula that go through many ramifications that perform different calculations, but i'm not getting the proper evaluation in custom column with an if-and-or formula. I simplified my formula to this simple one:

 

if (([Categorização Final] <> "Supplies" or [Categorização Final] <> "Packaging") and Date.Year([Data do Pedido]) < 2019) then "S<19" else "S>19"

 

I removed  "and Date.Year([Data do Pedido]) < 2019" in order to simplify more, but the "or" operator does not work.

 

In both cases i'm getting the "S<19" result for all records, even when output should be "S>19"

 

Note:

The field [Categorização Final] is set to Any (but I tried with the Text option too)

 

Original snippet

screenshot_01 2020.02.12 09h55.40.png

 

 

 

 

 

 

 

What is wrong in my code, can someone show me what i'm missing here?

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi  @Anonymous 

For your case, you just use 'and' instead of 'or' as below:

if (([Categorização Final] <> "Supplies" and [Categorização Final] <> "Packaging") and Date.Year([Data do Pedido]) < 2019) then "S<19" else "S>19"

 

Result:

6.JPG

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

hi  @Anonymous 

For your case, you just use 'and' instead of 'or' as below:

if (([Categorização Final] <> "Supplies" and [Categorização Final] <> "Packaging") and Date.Year([Data do Pedido]) < 2019) then "S<19" else "S>19"

 

Result:

6.JPG

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
tex628
Community Champion
Community Champion

Hi,

[Categorização Final] <> "Supplies" or [Categorização Final] <> "Packaging"

This is incorrent , there is no possible way for this to return false.

Since both the comparison operators are "<>" and then it's combined with an OR statement they cancel eachother out.

Br,
J


Connect on LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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