Optimize DAX

[mlozano]

helHello, I have the following problem.

I have the following formula, which is giving me the correct result, the drawback is that the performance is very slow. I would like to know if I have any way to optimize the performance of my measure. It is a very long formula so I could not add it to the publication, I leave a link with notepad that contains my measurement.

Measure 

[johnt75 · Accepted solution]

A quick win would be to use variables to store the results of calculations which are currently being done multiple times. e.g.

CALCULATE (
                        SUM ( Worksheet[LE Volume] ),
                        ALLSELECTED ( Worksheet ),
                        VALUES ( Worksheet[Month] ),
                        VALUES ( Worksheet[Category] )
                    )

is done multiple times and must be calculated every time. Store the result in a variable so you only have to do the calculation once.

[daXtreme · Accepted solution]

Hi @mlozano 

1. Must the measure be that long?

2. Have you removed all duplication from it?

3. Instead of IF's, have you tried to use SWITCHes?

4. Have you tried to optimize each small piece of the measure so that it's fast when applicable?

5. The problem is that if you have many variables calculated outside of IF/SWITCH, then DAX very often, or even 95%+ of the time, will try to calculate EVERYTHING regardless of whether a variable will or will not be needed for the final calculation. Therefore it's so important to place the calculation of variables in the correct places, that is under IF/SWITCH.

[johnt75 · Accepted solution]

A quick win would be to use variables to store the results of calculations which are currently being done multiple times. e.g.

CALCULATE (
                        SUM ( Worksheet[LE Volume] ),
                        ALLSELECTED ( Worksheet ),
                        VALUES ( Worksheet[Month] ),
                        VALUES ( Worksheet[Category] )
                    )

is done multiple times and must be calculated every time. Store the result in a variable so you only have to do the calculation once.