Forum Discussion
IFS and AND
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
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.
Can you post a screenshot of the error message?
selimovd there's no error message, it just reverts to 'off-target'
ameera_williams Then if it's not empty and not blank, what is the value when you want to get "tbd" back?
Can you post a screenshot? Is it a zero?
selimovd it is 'off-target'. See the RAG Status column on the far right