Power Pivot Formula Bogging down Excel

[BaldAccountant]

This is a power pivot question, not Power BI

I am trying to determine if I have a favorable or unfavorable variance with a dynamic formula and it is locking up excel.   I am not sure if it is my formula or if I am just trying to do too much in Power Pivot/Excel

Here is the first formula I tried :

Test P:=SWITCH(TRUE(),

[Selected good bad]="Good"&&[Test O]>=0,"Good",

[Selected good bad]="Good"&&[Test O]<0,"Bad",

[Selected good bad]="Bad"&&[Test O]<=0,"Good",

[Selected good bad]="Bad"&&[Test O]>0,"Bad",0)

Here is  the second one I tried:

Test Q:=SWITCH([Selected good bad],

"Good",IF([Test O]>=0,"Good","Bad"),

"Bad",IF([Test O]<=0,"Good","Bad"),0)

Please let me know if I am doing something wrong in my formulas.  Test O is a formula that calculates the difference between the filtered formula and the average for all doctors.  So then if it is a "Good: Stat, that means that a positive difference from the average would be favorable (i.e. More cases than average.   A "Bad" calculation is where a positive differnce would be unfavorable, such as a higher cost per case.  

Sorry I can't include my file because it contains HIPPA info.

[amitchandak]

@BaldAccountant , Try if this can help

Test Result :=
VAR _IsGood = [Selected good bad] = "Good"
VAR _Diff   = [Test O]
RETURN
IF(
    (_IsGood  && _Diff >= 0) ||
    (!_IsGood && _Diff <= 0),
    "Good",
    "Bad"
)

[BaldAccountant]

For some reason if I use a second variable for the "Bad" instead of ! _IsGood then it works.

Test Result:=VAR _IsGood = [Selected good bad] = "Good"

Var isbad = [Selected good bad]="Bad"

VAR _Diff = [Test O]

RETURN

IF(

(_IsGood && _Diff >= 0) ||

(isbad && _Diff <= 0),

"Good",

"Bad"

)

I am not sure what to do as far as marking a solution?  I will wait and if you can tell me what I did wrong using your solution I will mark yours as the solution.  I want you to get credit for tellin me to use variables.

[BaldAccountant]

I am getting this error

I am not sure why it is showing the extra two ")"  I just copied and pasted in your solution

[amitchandak · Accepted solution]

@BaldAccountant , I think it's a problem 
(!_IsGood && _Diff <= 0)

Try, if not can work(Power BI we use not )
(not _IsGood && _Diff <= 0)

[BaldAccountant]

That worked. Thanks