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
AZJohnPowerBI
Helper I
Helper I

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 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.




4 REPLIES 4
AZJohnPowerBI
Helper I
Helper I

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
Super User
Super User

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!!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





@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"

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.

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.