Forum Discussion

sarahomar955's avatar
sarahomar955
Regular Visitor
1 year ago
Solved

Dax query performance issue

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 ...
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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))
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

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))