Forum Discussion
Help with IF statement
Hello I am trying to use this IF statement on Power BI, but it is giving me errors:
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
8 Replies
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.
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,
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
Moved to the Desktop (DAX) forum. 😉
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,
PranitThank 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!
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)
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.
