Forum Discussion

AZJohnPowerBI's avatar
AZJohnPowerBI
Icon for Helper I rankHelper 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
Icon for Super User rankSuper 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
Icon for Helper I rankHelper I
2 years ago

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.