topic Re: Nested IF statement in DAX Commands and Tips

Nested IF statement

edcdcpowerbi — Fri, 10 Sep 2021 09:05:50 GMT

I am needing to add a third if statement below, which replaces blank values with a 0.

On its own it works fine, the other two if statements work without this third being added.

How do i correctly insert this Third IF statement, as it stands i get a semantic error saying 'Too many arguments passed, 3 is the maximum'

RETURN
IF(
ISBLANK( SliderValue ),0, SliderValue,
IF( SliderValue > 0, ProjRR,
IF(SliderValue = 0, Actual)
)
)

Re: Nested IF statement

goncalogeraldes — Fri, 10 Sep 2021 09:25:34 GMT

Hello there edcdcpowerbi , you already have three arguments in the highlighted part of your code. Therefore, all your arguments after it are causing the error. I think you can remove the "SliderValue" argument, or use a SWITCH() function instead of nested IFs() since it behaves almost like an IF THEN ELSE.

RETURN
IF(
ISBLANK( SliderValue ),0, SliderValue,
IF( SliderValue > 0, ProjRR,
IF(SliderValue = 0, Actual)
)
)

Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes

Re: Nested IF statement

edcdcpowerbi — Fri, 10 Sep 2021 09:34:03 GMT

Thanks for the help goncalogeraldes. i did try to remove SliderValue, but to no avail.

I did try the switch statement but it return True, instead of the values i wanted, where have i gone wrong?

SWITCH(SliderValue,
IF(ISBLANK(SliderValue),0,SliderValue),
SliderValue = 0, Actual,
SliderValue>0,ProjRR
)

Re: Nested IF statement

goncalogeraldes — Fri, 10 Sep 2021 09:35:01 GMT

edcdcpowerbitry this!

SWITCH( TRUE(), ISBLANK(SliderValue),0, SliderValue = 0, Actual, SliderValue > 0,ProjRR, SliderValue )

Re: Nested IF statement

edcdcpowerbi — Fri, 10 Sep 2021 09:39:18 GMT

goncalogeraldes it still displays (Blank) instead of zero im afraid

Re: Nested IF statement

goncalogeraldes — Fri, 10 Sep 2021 09:40:31 GMT

edcdcpowerbi please send the full formula then. What is the SliderValue?

Re: Nested IF statement

edcdcpowerbi — Fri, 10 Sep 2021 09:42:48 GMT

sure, here it is goncalogeraldes

VAR Actual = [Recycling Rate (PET)]
VAR AverageR = [Median Recycling Rate (PET)]
VAR SliderValue = [PETRecyclingRateOverride Value]
VAR ProjRR =
IF( SliderValue > 0, DIVIDE( SliderValue, 100, 0 ), Actual ) //var AverageRR = IF (SliderValue > 0, DIVIDE(SliderValue,100,0), AverageR)
RETURN
//IF ( SELECTEDVALUE (Jurisdiction[DRS Status]) = "Existing DRS" , ProjRR, AverageRR)

SWITCH(
TRUE(),
ISBLANK(SliderValue),0,
SliderValue = 0, Actual,
SliderValue > 0,ProjRR,
SliderValue
)

Re: Nested IF statement

goncalogeraldes — Fri, 10 Sep 2021 09:55:06 GMT

The way DAX handles BLANKS can be somewhat confusing so in this case it can be something related to DAX never considering the value to be blank and evaluating it as FALSE everytime. A possible workaround can be:

SWITCH( TRUE(), SliderValue = BLANK(), 0, SliderValue = 0, Actual, SliderValue > 0, ProjRR, SliderValue )

Check out this article as it may help you in this case or in a similar one in the future!

Re: Nested IF statement

edcdcpowerbi — Fri, 10 Sep 2021 09:54:44 GMT

that worked perfectly, thank you!

Re: Nested IF statement

goncalogeraldes — Fri, 10 Sep 2021 09:55:58 GMT

Glad to be of help!