topic Re: Regarding a (specific) nested IF formula results in DAX Commands and Tips

Regarding a (specific) nested IF formula results

EsgibtkeinBier — Tue, 05 Jul 2022 16:42:37 GMT

Hey everyone!

I was wondering how the following formula produces results for dates > (2021,3,1), since I can't wrap my head around how it handles stuff after the aforementioned date! Thank you for your time!

I =

IF('Overview'[Date] < Date(2020,4,1),
IF('Overview'[Buyer Type]="A",
IF('Overview'[Auction Type]="O",
Divide(Overview[r],76)*100*0.21,
Divide(Overview[r],85)*100*0.11),
Divide(Overview[r],73)*100*0.20),

IF('Overview'[Date] < Date(2020,6,1),
IF('Overview'[Buyer Type]="A",
IF('Overview'[Auction Type]="O",
Divide(Overview[r],77)*100*0.20,
Divide(Overview[r],87)*100*0.1),

Divide(Overview[r],74)*100*0.20),

IF('Overview'[Date] < Date(2020,11,1),
IF('Overview'[Buyer Type]="A",
IF('Overview'[Auction Type]="O",
Divide(Overview[r],73.5)*100*0.235,
Divide(Overview[r],87)*100*0.1),
Divide(Overview[r],70.5)*100*0.235),

IF(Overview[Date] < date(2021,3,1),
IF(Overview[Publisher]="E",
0,
IF(Overview[A]="A",
DIVIDE(Overview[r],87)*100*0.13,
Divide(Overview[i],500)
)
)
,
IF(Overview[Publisher]="E",
0,
IF(Overview[A]="A",
DIVIDE(Overview[r],87)*100*0.13,
If(Overview[A]="V",
DIVIDE(Overview[i],250),
Divide(Overview[i],500)
)
)
)))))

Re: Regarding a (specific) nested IF formula results

daXtreme — Tue, 05 Jul 2022 16:49:37 GMT

Hi EsgibtkeinBier

I wouldn't even try to understand it 🙂 I would rewrite it using SWITCH instead of IF's. If you start rewriting it, you'll sooner or later understand everything about it. That's the by-product of making code clearer with the correct structures.

Re: Regarding a (specific) nested IF formula results

Anonymous — Fri, 08 Jul 2022 02:26:13 GMT

Hi EsgibtkeinBier

As daXtreme said, when you have multiple nests, it is better to use SWITCHE function instead of IF function . In terms of execution speed, SWITCHE is faster than IF . And SWITCHE is easier to edit and have a clear hierarchy.

https://www.scaler.com/topics/c/difference-between-if-else-and-switch/

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Regarding a (specific) nested IF formula results

EsgibtkeinBier — Sun, 31 Jul 2022 14:21:33 GMT

Thanks everyone for suggesting to use SWITCH, as it makes sense both in terms of performance and clarity! However, this does not answer the question of how dates > (2021,3,1) are handled.

With a careful look at the expression above, one can see that only the last logical test involving dates has an else statement, therefore, all dates after the 1st of March 2021, are handled by this part of the expression:

IF(Overview[Publisher]="E",
0,
IF(Overview[A]="A",
DIVIDE(Overview[r],87)*100*0.13,
If(Overview[A]="V",
DIVIDE(Overview[i],250),
Divide(Overview[i],500)