Forum Discussion
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<=50%,"Off-Target")
What that formula is trying to say is:
IF the value in column G2 ("% Completed vs Planned") is more than 50% and less than 89% then it should be labelled 'At Risk'.
Or IF the value in column G2 ("% Completed vs Planned") is more than 90% then it should be labelled as 'On-Target'
and finally, IF the value in column G2 ("% Completed vs Planned") is less than or equal to 50% then it should be labelled as 'Off-Target'.
So essentially, there are 3 criterias.
Help would be greatly appreciated!
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
14 Replies
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.biselimovd Actually, if the cell in '% Completed vs Planned' is empty/blank, then i want to return 'tbd'. What would i need to add to the DAX calc to return this?
Hey ameera_williams ,
you can just add it to the switch function:
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] = "" || 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.bi
selimovd Thank you so much for this quick response and for the solution. It worked!! And appreciate you linking the article, it was very helpful. Thanks a lot.
