Forum Discussion
Solved
IFS and AND
Hello, i am trying to write the equivalent of an IFS AND statement in Power BI and am strugling. In Excel, i have written it like this: =IFS(AND(G2>50%,G2<89%),"At-Risk",G2>90%,"On-Target",G2<=...
Hey ameera_williams ,
try the following calculated column:
My IFS Column = SWITCH ( TRUE (), myTable[% Completed vs Planned] > 0.5 && myTable[% Completed vs Planned] < 0.89, "At Risk", myTable[% Completed vs Planned] > 0.9, "On Target", myTable[% Completed vs Planned] <= 0.5, "Off Target" )You can check for multiple criteria the best with the combination of SWITCH and TRUE. Check also the following article for that:
https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/
If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.bi
Hey ameera_williams ,
can you post a screenshot of the error?
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
selimovd Please see screenshot above. It might be because the % completed vs planned column has a formula in it already it is not detecting it as 'blank'?
Hey ameera_williams ,
no it is because you are comparing a number against a string ("").
Try the following formula, this should work:
My IFS Column = SWITCH ( TRUE (), myTable[% Completed vs Planned] > 0.5 && myTable[% Completed vs Planned] < 0.89, "At Risk", myTable[% Completed vs Planned] > 0.9, "On Target", myTable[% Completed vs Planned] <= 0.5, "Off Target", myTable[% Completed vs Planned] = BLANK(), "tbd" )If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biselimovd Thanks for your reply. I had already tried that originally. Unfortunately it didn't work. Thanks for your help.