IF Statement Performance Issue ( calculating 2nd False part even if condition is true )

[sajidmansoorali]

I have a performance issue while using IF condition in DAX Expression. 

if i remove the 2nd part of if statment and simply add 0.  Result Calculated: 03 Seconds Only 

if i kept the every thing as it is. Restult Calcuated Time: 4 to 5 mintues (even if only one BL is selected).

My observation is that "if condition" is always calcuate the false part even if the condition is true while there is no need to calculate 2nd condition. 

Measure Projected Closing =
VAR BLs =
VALUES ( SalesCurr[BL_CODE1] )
VAR RDB =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "RDB", BLs ),
0
)
VAR KLG =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "KLG", BLs ),
0
)
VAR UBJ =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "UBJ", BLs ),
0
)
VAR HLB =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "HLB", BLs ),
0
)
VAR PGL =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "PGL", BLs ),
0
)
VAR MRS =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "MRS", BLs ),
0
)
VAR IFC =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "IFC", BLs ),
0
)
VAR AXE =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "AXE", BLs ),
0
)
VAR LNT =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "LNT", BLs ),
0
)
VAR LRL =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "LRL", BLs ),
0
)
VAR DSS =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "DSS", BLs ),
0
)
VAR MBR =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "MBR", BLs ),
0
)
VAR WRG =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "WRG", BLs ),
0
)
VAR OVL =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "OVL", BLs ),
0
)
VAR SHK =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "SHK", BLs ),
0
)
VAR HIZ =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "HIZ", BLs ),
0
)
VAR BLG =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "BLG", BLs ),
0
)
VAR GAN =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "GAN", BLs ),
0
)
VAR NOR =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "NOR", BLs ),
0
)
VAR TNG =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "TNG", BLs ),
0
)
VAR BL_1 =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], VALUES ( SalesCurr[BL_CODE1] ) ),
0
)
RETURN
IF (
HASONEVALUE ( SalesCurr[BL_CODE1] ),
BL_1,
RDB+KLG+UBJ+HLB+PGL+MRS+IFC+AXE+LNT+LRL+DSS+MBR+WRG+OVL+SHK+HIZ+BLG+GAN+NOR+TNG)

[v-qiuyu-msft]

Hi @sajidmansoorali,

In your scenario, IFERROR will cause performance issue, please avoid using if you really need it. It is not that they are slow by themselves. The problem is that the DAX engine cannot use optimized paths in its code when errors happen. In most cases, it is more efficient to check operands for possible errors instead of using the error-handling engine. For example, instead of writing this:
= IFERROR ( SQRT ( Test[Omega] ), BLANK () )

It is much better to write this:
= IF ( Test[Omega] >= 0, SQRT ( Test[Omega] ), BLANK () )

This second expression does not need to detect the error and it is faster than the previous one. This, of course, is a general rule.

In your measure, there is a part: 

VAR BLs =
VALUES ( SalesCurr[BL_CODE1] )
VAR RDB =
IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "RDB", BLs ),
0
)

There are two filter conditions in CALCULATE() function, as these two filter arguments affect the same column [BL_CODE1], they are merged together using an AND operator (or, in mathematical terms, using the set intersection).

So I would like to what do you really want? Please share pbix file with us and clarify the desired results so we can try to optimize DAX. 

Best Regards,
Qiuyun Yu 

[sajidmansoorali]

Thanks @Vicky_Song

let me try the approch you provided. will update soon.

[sajidmansoorali]

@Vicky_Song

IFERROR (
CALCULATE ( [Gross Sales] / [LD_AVG_%age], SalesCurr[BL_CODE1] = "RDB", BLs ),
0
)

if above statment give me result of infinity, how can i handle infinity with IF condition without using IFERROR can you please help.