Help with IF statement

[OPS-MLTSD]

Hello I am trying to use this IF statement on Power BI, but it is giving me errors:

3 Yr LTI Risk = IF('IHSP - work plan'[3-Yr LTI per 100] >=5, "High", IF('IHSP - work plan'[3-Yr LTI per 100] <1, "Low", IF('IHSP - work plan'[3-Yr LTI per 100] >=1, 'IHSP - work plan'[3-Yr LTI per 100] <2, "Medium", "Not Available")))

Note: I have NULL in my data, would that cause any issues?

This is the error message I am getting:

[v-alq-msft]

Hi, @OPS-MLTSD 

Based on your description, I created data to replace your scenario. The pbix file is attached in the end.

IHSP - work plan:

You may create a calculated column as below.

Result = 
SWITCH(
    TRUE(),
    [3-Yr LTI per 100]<1,"Low",
    [3-Yr LTI per 100]>=1&&[3-Yr LTI per 100]<2,"Medium",
    [3-Yr LTI per 100]>=2&&[3-Yr LTI per 100]<5,"Not Available",
    [3-Yr LTI per 100]>=5,"High"
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

HI @OPS-MLTSD 

You need to revisit the range of 'IHSP - work plan'[3-Yr LTI per 100]

as per your formula

>=5	High
<1	Low
>=1(is it 'and <2'?????)	Medium
>=2 to <5(Ths range is not present)	???????(lets assume Medium High)

Try this

3 Yr LTI Risk = IF('IHSP - work plan'[3-Yr LTI per 100] >=5, "High",
                                   IF('IHSP - work plan'[3-Yr LTI per 100] <1, "Low",
                                                     IF('IHSP - work plan'[3-Yr LTI per 100] >=1 && 'IHSP - work plan'[3-Yr LTI per 100] <2, "Medium",

                                                                      IF('IHSP - work plan'[3-Yr LTI per 100] >=2 && 'IHSP - work plan'[3-Yr LTI per 100] <5,"Medium High",

                                                                     "Not Available")

                                                       )

                                        )

                          )

Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!

Regards,
Pranit

[OPS-MLTSD]

Thank you, I tried your formula, however, I noticed that it is not applying to the decimal values; for example - 5.69 should be considered "High" but the category I am getting for 5.69 is "Not Available, if you could please let me know how I can resolve this, that would be much appreciated!

[harshnathani]

Hi @OPS-MLTSD ,

Works for 5.69 using the same formula.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[mahoneypat]

This the Power Query board and that is a DAX expression, but no problem.  You have 4 terms in your last IF.  You need to combine the >=1 and <2 parts with an AND() or connect them with "&&" so you check it returns true only if both are true.  In any case, it would be better to write this expression using the SWITCH(TRUE(), ...) pattern.  Please see this article.  It is a much better way to write nexted IFs like this.

https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[edhans]

Moved to the Desktop (DAX) forum. 😉

[camargos88]

Hi @OPS-MLTSD ,

This part:

IF('IHSP - work plan'[3-Yr LTI per 100] >=1, 'IHSP - work plan'[3-Yr LTI per 100] <2, "Medium", "Not Available")

is not correct, here you have 4 inputs for the if function, it requires 3.

[OPS-MLTSD]

Yes I took off the "Not Available" and I get this error:

Just wondering if anyone can help me figure out how i can solve my problem?

thank you,