DAX: Caching And IF Statement Optimization

Kevin Feasel

2018-06-05

DAX

Marco Russo shows us a way of improving performance on conditional statements:

Consider the following measure.

1
2
3
4
5
Margin :=
IF (
    [Sales Amount] > 0 && [Total Cost] > 0,
    [Sales Amount] - [Total Cost]
)

The basic idea is that the difference between Sales Amount and Total Cost should be evaluated only whether both measures are greater than zero. In such a condition, the DAX engine produces a query plan that evaluates each measure twice. This is visible in the storage engine requests generated for the following query.

Read on to see how Marco avoids this performance issue.

Related Posts

Counting Working Days with DAX

Alberto Ferrari shows how we can ignore weekends in date calculations with DAX: How is it possible to compute the difference between the two dates, only computing working days and skipping weekends and holidays? Simple math is no longer useful here, and DAX does not offer a predefined function. A solution to this scenario requires […]

Read More

Formatting Lists of Values with DAX

Alberto Ferrari and Patrick LeBlanc have a great video on formatting lists of filter values in DAX like 2003, 2005-2007, 2009: Alberto Ferrari joins Patrick to walk through how you can use DAX to format a list of values within Power BI Desktop. This takes the concatenate values quick measure to the next level. Transmuting […]

Read More

Categories

June 2018
MTWTFSS
« May Jul »
 123
45678910
11121314151617
18192021222324
252627282930