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

1 Reply

  • bhanu_gautam's avatar
    bhanu_gautam
    Super 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))