Redundant ‘IF(Divide)’ Statement and Power BI Report Performance Issues

[monaraya]

In this blog, I will explain how a redundant ‘Return’ statement in a Power BI Measure can cause huge performance issues.

Issue:

One of the customers has created a beautiful report with multiple pages and visuals. All but one visuals load within a few seconds. A specific visual called ‘Growth Rate’ used to take 3 minutes to render the data, even though the report is in ‘import’ mode.

The underlying measure looks like below:

Cumulative Growth Rate_ =

VAR Cumulative_Growth =

CALCULATE (

[TotalGrowthwithTotalTrends],

FILTER (

ALLSELECTED ( TABLE1 ),

TABLE1[Month Start] <= MAX ( TABLE1[Month Start] )

)

)

VAR Cumulative_Sales =

CALCULATE (

[TotalSalesTrend],

FILTER (

ALLSELECTED ( PMS_TM1_SALES_VOLUME ),

[Month Start] <= MAX ( TABLE1[Month Start] )

)

)

RETURN

IF (

DIVIDE ( Cumulative_Growth, Cumulative_Sales, 0 ) <> 0,

DIVIDE ( Cumulative_Growth, Cumulative_Sales, 0 ))

Resolution:

If you look  at the above DAX Query, the RETURN statement IF…DIVIDE…DIVIDE is redundant. We have changed the RETURN statement to DIVIDE ( Cumulative_Growth, Cumulative_Sales, 0) and now the visual loads in 20 seconds.

Author: Mounika Narayana Reddy