topic Re: Power Pivot Formula Bogging down Excel in DAX Commands and Tips

Power Pivot Formula Bogging down Excel

BaldAccountant — Tue, 25 Nov 2025 14:42:24 GMT

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.

Re: Power Pivot Formula Bogging down Excel

amitchandak — Tue, 25 Nov 2025 14:52:41 GMT

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" )

Re: Power Pivot Formula Bogging down Excel

BaldAccountant — Tue, 25 Nov 2025 15:57:49 GMT

I am getting this error

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

Re: Power Pivot Formula Bogging down Excel

BaldAccountant — Tue, 25 Nov 2025 16:15:45 GMT

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.

Re: Power Pivot Formula Bogging down Excel

amitchandak — Tue, 25 Nov 2025 16:23:44 GMT

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

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

Re: Power Pivot Formula Bogging down Excel

BaldAccountant — Tue, 25 Nov 2025 16:50:31 GMT

That worked. Thanks