Forum Discussion

AZJohnPowerBI's avatar
AZJohnPowerBI
Helper I
2 years ago

if statement isn't working

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 wh...
Need Help
Tips and Tricks
rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

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's avatar
AZJohnPowerBI
Helper I
2 years ago

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 NameDateSupervisor 1st ColSupervisor 2nd Col
Agent112/27/2023Mickey 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"