if statement isn't working

[AZJohnPowerBI]

I have a lengthy if and statement for a slowly-changing dimension (SCD), as agents in my company come and go, and move around.

I have tried each piece of the code separately, and it works, but when I put the code together, it fails. I've distilled it down to the names that repeat, and that is what is breaking the code.

For example, I'm making a Supervisor column, which the code looks something like:

if

[Date] > #date(2020/1/1) and [Date] < #date(2022/3/1) and [Agent Name] = "Mickey Mouse" or

[Date] > #date(2020/1/1) and [Date] < #date(2022/3/1) and [Agent Name] = "Minnie Mouse" or

[Date] > #date(2020/1/1) and [Date] < #date(2022/3/1) and [Agent Name] = "Goofy"
then
"Donald Duck"
else if

[Date] > #date(2022/3/2) and [Date] < #date(2024/3/1) and [Agent Name] = "Mickey Mouse" or

[Date] > #date(2022/3/2) and [Date] < #date(2024/3/1) and [Agent Name] = "Minnie Mouse" or

[Date] > #date(2022/3/2) and [Date] < #date(2024/3/1) and [Agent Name] = "Goofy"
then
"Stinky Pete"
else
""
Most of the code works fine, until I get to the agents whose supervisor changed. The agent names are the same, but the dates were changed. The error statement reads
[Expression.Error] The Date operation failed because the resulting value falls outside the range of allowed values.

[AZJohnPowerBI]

So the code is so long that it exceeds the maximum amount of chars haha, but I no longer think it's simply "the resulting value is outside the range", as that would mean the agent is outside the dates given. But I'm not sure how to move forward.

[rajendraongole1]

Hi @AZJohnPowerBI - You can rewrite your if-else statement as below:

if
([Date] > #date(2020, 1, 1) and [Date] < #date(2022, 3, 1) and [Agent Name] = "Mickey Mouse") or
([Date] > #date(2020, 1, 1) and [Date] < #date(2022, 3, 1) and [Agent Name] = "Minnie Mouse") or
([Date] > #date(2020, 1, 1) and [Date] < #date(2022, 3, 1) and [Agent Name] = "Goofy")
then
"Donald Duck"
else if
([Date] > #date(2022, 3, 2) and [Date] < #date(2024, 3, 1) and [Agent Name] = "Mickey Mouse") or
([Date] > #date(2022, 3, 2) and [Date] < #date(2024, 3, 1) and [Agent Name] = "Minnie Mouse") or
([Date] > #date(2022, 3, 2) and [Date] < #date(2024, 3, 1) and [Agent Name] = "Goofy")
then
"Stinky Pete"
else
""

By adding parentheses around each individual condition, you ensure that each and condition is evaluated together before applying the or operator.

You can add a step to filter out invalid date entries before performing your if-else checks

Check the above logic and let know.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[AZJohnPowerBI]

@rajendraongole1 @amitchandak @Greg_Deckler 
I split up the code into four separate columns and only the second didn't work. Here's the code:

When I placed them side-by-side, the errors were for agents listed below, and go figure, it is because the agents have the same name, so it doesn't know whether or not the supervisor should be Mickey or Minnie Mouse.

The weird thing is, the criteria [Date] > #date(2024,3,1) ... doesn't seem to be registering. The first line shows Mickey Mouse as the supervisor in column 1, but column 2 shows an error.

Agent Name	Date	Supervisor 1st Col	Supervisor 2nd Col
Agent1	12/27/2023	Mickey Mouse	Error (Expression.Error: The Date operation failed because the resulting value falls outside the range of allowed values.)

if

([Date] > #date (2022,11,14) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent1" ) or
([Date] > #date (2021,5,10) and [Date] < #date (2023,8,23) and [Agent Name] = "Agent2" ) or
([Date] > #date (2023,1,1) and [Date] < #date (2023,3,17) and [Agent Name] = "Agent3" ) or
([Date] > #date (2023,12,18) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent4" ) or
([Date] > #date (2022,8,17) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent5" ) or
([Date] > #date (2020,10,26) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent6" ) or
([Date] > #date (2023,6,12) and [Date] < #date (2023,8,1) and [Agent Name] = "Agent7" ) or
([Date] > #date (2023,1,1) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent8" ) or
([Date] > #date (2023,1,23) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent9" ) or
([Date] > #date (2023,1,1) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent10" ) or
([Date] > #date (2023,1,1) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent11" ) or
([Date] > #date (2023,8,7) and [Date] < #date (2023,11,27) and [Agent Name] = "Agent12" ) or
([Date] > #date (2022,8,8) and [Date] < #date (2023,8,18) and [Agent Name] = "Agent13" ) or
([Date] > #date (2023,10,16) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent14" ) or
([Date] > #date (2023,1,1) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent15" ) or
([Date] > #date (2022,12,5) and [Date] < #date (2023,8,26) and [Agent Name] = "Agent16" ) or
([Date] > #date (2020,9,14) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent17" ) or
([Date] > #date (2020,10,14) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent18" ) or
([Date] > #date (2017,12,18) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent19" ) or
([Date] > #date (2003,1,27) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent20" ) or
([Date] > #date (2022,12,22) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent21" ) or
([Date] > #date (2003,8,4) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent22" ) or
([Date] > #date (2023,8,21) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent23" ) or
([Date] > #date (2023,1,1) and [Date] < #date (2023,2,23) and [Agent Name] = "Agent24" ) or
([Date] > #date (2023,5,15) and [Date] < #date (2024,3,1) and [Agent Name] = "Agent25" ) or
([Date] > #date (2023,1,1) and [Date] < #date (2023,3,1) and [Agent Name] = "Agent26" )
then
"Mickey Mouse"

else if
([Date] >= #date (2024,3,1) and [Agent Name] = "Agent1" ) or
([Date] >= #date (2024,3,1) and [Agent Name] = "Agent2" ) or
([Date] >= #date (2024,3,1) and [Agent Name] = "Agent3" ) or
([Date] >= #date (2024,3,1) and [Agent Name] = "Agent4" ) or
([Date] >= #date (2024,3,1) and [Agent Name] = "Agent5" ) or
([Date] >= #date (2024,3,1) and [Agent Name] = "Agent6" ) or
([Date] >= #date (2024,3,1) and [Agent Name] = "Agent7" ) or
([Date] >= #date (2024,3,1) and [Agent Name] = "Agent8" ) or
([Date] >= #date (2024,3,1) and [Agent Name] = "Agent9" ) or
([Date] >= #date (2024,3,1) and [Agent Name] = "Agent0" ) or
([Date] >= #date (2024,3,1) and [Agent Name] = "Agent1" ) or
([Date] >= #date (2024,3,1) and [Agent Name] = "Agent2" ) or
([Date] >= #date (2024,3,1) and [Agent Name] = "Agent3" ) or
([Date] >= #date (2024,3,1) and [Agent Name] = "Agent4" ) or
([Date] >= #date (2024,3,1) and [Agent Name] = "Agent5" ) or
([Date] >= #date (2024,3,1) and [Date] < #date (2024,5,1) and [Agent Name] = "Agent26" )
then
"Minnie Mouse"

[AZJohnPowerBI]

That helped clean up quite a bit!

Here's what is weird about this,

If I put in one block or even several, it'll work.

Also, if I put in [Agent Name] = "Donald Duck"...

it'll work, but if I put Donald in a list, then sometimes, it spits out the same error.

I will put in the entire code in a little bit, I just need to change the names.