Dax query performance issue

sarahomar955 — Wed, 12 Feb 2025 10:47:02 GMT

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?

Re: Dax query performance issue

bhanu_gautam — Wed, 12 Feb 2025 11:18:06 GMT

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 =