Dax query performance issue

[sarahomar955]

Hello, i have 2 measures, the second one calls the first one but i need to optimize them since they are taking long time to compute with large data:

Measure 1:

L10\ = DIVIDE(CALCULATE(SUM('SBI Actuals Agg (x100)'[Actuals]),FILTER(ALLEXCEPT('SBI Actuals Agg (x100)',

'SBI Actuals Agg (x100)'[pc],'SBI Actuals Agg (x100)'[Country],

'SBI Actuals Agg (x100)'[ch],'SBI Actuals Agg (x100)'[mc],

'SBI Actuals Agg (x100)'[brand], 'SBI Actuals Agg (x100)'[ch3]),'SBI Actuals Agg (x100)'[Line of Items]="[L10]"))

,(CALCULATE(SUM('SBI Actuals Agg (x100)'[Actuals]),FILTER(ALLEXCEPT('SBI Actuals Agg (x100)','SBI Actuals Agg (x100)'[pc],

'SBI Actuals Agg (x100)'[Country],'SBI Actuals Agg (x100)'[ch],'SBI Actuals Agg (x100)'[mc],  'SBI Actuals Agg (x100)'[brand],

 'SBI Actuals Agg (x100)'[ch3]),'SBI Actuals Agg (x100)'[Line of Items]="[GES]"))-CALCULATE(SUM('SBI Actuals Agg (x100)'[Actuals]),

 FILTER(ALLEXCEPT('SBI Actuals Agg (x100)','SBI Actuals Agg (x100)'[pc],'SBI Actuals Agg (x100)'[Country],'SBI Actuals Agg (x100)'[ch],

 'SBI Actuals Agg (x100)'[mc],  'SBI Actuals Agg (x100)'[brand], 'SBI Actuals Agg (x100)'[ch3]),'SBI Actuals Agg (x100)'[Line of Items]="[L6a]"))),0)

Measure 2:

L10_time_new = [L10\]

*

(CALCULATE([selected_customers_total],FILTER(ALLEXCEPT('SBI Actuals Agg (x100)','SBI Actuals Agg (x100)'[pc],

'SBI Actuals Agg (x100)'[Country],'SBI Actuals Agg (x100)'[ch],'SBI Actuals Agg (x100)'[mc], 'SBI Actuals Agg (x100)'[brand], 'SBI Actuals Agg (x100)'[ch3]),

'SBI Actuals Agg (x100)'[Line of Items]="[GES]"))

-

CALCULATE([selected_customers_total],FILTER(ALLEXCEPT('SBI Actuals Agg (x100)','SBI Actuals Agg (x100)'[pc],

'SBI Actuals Agg (x100)'[Country],'SBI Actuals Agg (x100)'[ch],'SBI Actuals Agg (x100)'[mc],  'SBI Actuals Agg (x100)'[brand], 'SBI Actuals Agg (x100)'[ch3]),

'SBI Actuals Agg (x100)'[Line of Items]="[L6a]")))

 +

 ([L10\]

 *

 (CALCULATE([selected_customers_total],FILTER(ALLEXCEPT('SBI Actuals Agg (x100)'

,'SBI Actuals Agg (x100)'[pc],'SBI Actuals Agg (x100)'[Country],'SBI Actuals Agg (x100)'[ch],

'SBI Actuals Agg (x100)'[mc],  'SBI Actuals Agg (x100)'[brand], 'SBI Actuals Agg (x100)'[ch3]),'SBI Actuals Agg (x100)'[Line of Items]="[GES]"))

-

CALCULATE([selected_customers_total],

FILTER(ALLEXCEPT('SBI Actuals Agg (x100)','SBI Actuals Agg (x100)'[pc],'SBI Actuals Agg (x100)'[Country],

'SBI Actuals Agg (x100)'[ch],'SBI Actuals Agg (x100)'[mc],  'SBI Actuals Agg (x100)'[brand], 'SBI Actuals Agg (x100)'[ch3]),'SBI Actuals Agg (x100)'[Line of Items]="[L6a]")))

*

(L10[L10 Value]/100))

Can you help me optimize these dax queries for better performance?

[bhanu_gautam · Accepted solution]

@sarahomar955 , Try using variables

For measure 1

dax
L10 =
VAR Actuals_L10 =
CALCULATE(
SUM('SBI Actuals Agg (x100)'[Actuals]),
FILTER(
ALLEXCEPT(
'SBI Actuals Agg (x100)',
'SBI Actuals Agg (x100)'[pc],
'SBI Actuals Agg (x100)'[Country],
'SBI Actuals Agg (x100)'[ch],
'SBI Actuals Agg (x100)'[mc],
'SBI Actuals Agg (x100)'[brand],
'SBI Actuals Agg (x100)'[ch3]
),
'SBI Actuals Agg (x100)'[Line of Items] = "[L10]"
)
)

VAR Actuals_GES =
CALCULATE(
SUM('SBI Actuals Agg (x100)'[Actuals]),
FILTER(
ALLEXCEPT(
'SBI Actuals Agg (x100)',
'SBI Actuals Agg (x100)'[pc],
'SBI Actuals Agg (x100)'[Country],
'SBI Actuals Agg (x100)'[ch],
'SBI Actuals Agg (x100)'[mc],
'SBI Actuals Agg (x100)'[brand],
'SBI Actuals Agg (x100)'[ch3]
),
'SBI Actuals Agg (x100)'[Line of Items] = "[GES]"
)
)

VAR Actuals_L6a =
CALCULATE(
SUM('SBI Actuals Agg (x100)'[Actuals]),
FILTER(
ALLEXCEPT(
'SBI Actuals Agg (x100)',
'SBI Actuals Agg (x100)'[pc],
'SBI Actuals Agg (x100)'[Country],
'SBI Actuals Agg (x100)'[ch],
'SBI Actuals Agg (x100)'[mc],
'SBI Actuals Agg (x100)'[brand],
'SBI Actuals Agg (x100)'[ch3]
),
'SBI Actuals Agg (x100)'[Line of Items] = "[L6a]"
)
)

RETURN
DIVIDE(Actuals_L10, Actuals_GES - Actuals_L6a, 0)

and for measure 2

dax

L10_time_new =

VAR L10_Value = [L10]

VAR Selected_Customers_GES =

    CALCULATE(

        [selected_customers_total],

        FILTER(

            ALLEXCEPT(

                'SBI Actuals Agg (x100)',

                'SBI Actuals Agg (x100)'[pc],

                'SBI Actuals Agg (x100)'[Country],

                'SBI Actuals Agg (x100)'[ch],

                'SBI Actuals Agg (x100)'[mc],

                'SBI Actuals Agg (x100)'[brand],

                'SBI Actuals Agg (x100)'[ch3]

            ),

            'SBI Actuals Agg (x100)'[Line of Items] = "[GES]"

        )

    )

VAR Selected_Customers_L6a =

    CALCULATE(

        [selected_customers_total],

        FILTER(

            ALLEXCEPT(

                'SBI Actuals Agg (x100)',

                'SBI Actuals Agg (x100)'[pc],

                'SBI Actuals Agg (x100)'[Country],

                'SBI Actuals Agg (x100)'[ch],

                'SBI Actuals Agg (x100)'[mc],

                'SBI Actuals Agg (x100)'[brand],

                'SBI Actuals Agg (x100)'[ch3]

            ),

            'SBI Actuals Agg (x100)'[Line of Items] = "[L6a]"

        )

    )

VAR L10_Part = L10_Value * (Selected_Customers_GES - Selected_Customers_L6a)

RETURN

    L10_Part + (L10_Value * (Selected_Customers_GES - Selected_Customers_L6a) * (L10[L10 Value] / 100))