If stops at 3 else's?

[Craigc3814]

I just learned, unfortunatley the hard way that after the 3rd IF this formula quits working. I believe Switch is the correct way to go? But I am unsure how to loop in Lookupvalue due to lack of examples found while researching. 

As menitoned the first 3 IF's work then apparently PowerBI does not let you do anymore?

Fy22 = IF(Construction[Cashflow Tpye]="Back-Loaded",LOOKUPVALUE(Curves[Back Loaded Current],Curves[Percent Complete],Construction[FY22%Comp]),

IF(Construction[Cashflow Tpye]="Trapezoid",LOOKUPVALUE(Curves[Trapezoid Current],Curves[Percent Complete],Construction[FY22%Comp]),

IF(Construction[Cashflow Tpye]="Slow",LOOKUPVALUE(Curves[Slow Curve Current],Curves[Percent Complete],Construction[FY22%Comp],

if(Construction[Cashflow Tpye]="Front-Loaded",LOOKUPVALUE(Curves[Front Loaded Current],Curves[Percent Complete],Construction[FY22%Comp],

if(Construction[Cashflow Tpye]="Linear",Construction[FY22%Comp],0)))))))

[jdbuchanan71]

Yep, SWITCH would be the way to do it.  The formula from @AlB  shows you how that would work.

[jdbuchanan71 · Accepted solution]

@Craigc3814 

IF can go deeper than 3, you are missing some closing ) on some LOOKUPVALUE lines.

That being said, SWITCH is the way to go and @AlB gave you the solution for that.

[Craigc3814]

Someone pointed out the mistake I made above and I fixed it.

What is the better way of going about this? Your formula worked perfectly! I assume Switch is less cumbersome on the query? I love learning this stuff!

This community never ceases to amaze me

[Craigc3814]

WOW you are correct, that fixed the issue. It was a complete coincedence that I read this article and my formula being out of whack after the 3rd instance. THANK YOU SO MUCH

https://community.powerbi.com/t5/Desktop/Is-there-a-maximum-to-the-number-of-else-if-statements-in-a...

[AlB]

Hi @Craigc3814 

I do NOT believe IF fails after three nested instances. There must be something else going on. In any case, if you want that code with SWITCH() :

Fy22 =
SWITCH (
    Construction[Cashflow Type],
    "Back-Loaded",
        LOOKUPVALUE (
            Curves[Back Loaded Current],
            Curves[Percent Complete], Construction[FY22%Comp]
        ),
    "Trapezoid",
        LOOKUPVALUE (
            Curves[Trapezoid Current],
            Curves[Percent Complete], Construction[FY22%Comp]
        ),
    "Slow",
        LOOKUPVALUE (
            Curves[Slow Curve Current],
            Curves[Percent Complete], Construction[FY22%Comp]
        ),
    "Front-Loaded",
        LOOKUPVALUE (
            Curves[Front Loaded Current],
            Curves[Percent Complete], Construction[FY22%Comp]
        ),
    "Linear", Construction[FY22%Comp],
    0
)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Anonymous]

No, you can use many If's as you want. I use a query with 15 to 20 If's sequentially. Maybe your logic is stopping before for its constructions?

[Craigc3814]

From what I read if you are using Direct Query mode there is a limit of 3 (found in another forum answer) but in import mode there is no limit. I am assuming that is correct because my IF statement stops working at 3