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

Quartiles In DAX

Dustin Ryan shows us how to calculate quartiles using DAX: To calculate the quartile, we’re going to use the PERCENTILEX.INC DAX function. The PERCENTILEX.INC function returns the number at the specified percentile. So for example, if I had numbers 0 and 100 in my data set, the 25th percentile value would be 25. The 50th percentile […]

Read More

The Basics Of DAX

Kevin Feasel

2018-08-22

DAX

Matthew Brice walks us through filters and calculations in DAX: CALCULATE is somewhat unique in that it evaluates the 2nd, 3rd, …nth parameter first, and evaluates the first parameter last using values from my Filter Context Box. I think it is extremely helpful to list briefly the steps CALCULATE performs whenever it is invoked. (So maybe we are not […]

Read More

Categories

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