Forum Discussion
How to improve performance issues for DAX Formula
- 3 years ago
I agree with the suggestion to use the ADDCOLUMNS pattern. Here are two other ways to write your expression (I think), but the [01. Order Units] measure may be the reason for slowness. What is the expression for that measure?
Option 1_X̄Production LT =
VAR summary =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE (
'PO Daybook',
'PO Daybook'[__.01. Production LT],
'PO Daybook'[PO Number]
),
"cOrdUnits", [01. Ordered Units]
),
KEEPFILTERS ( 'Product'[Newness Classification] <> "Collab" ),
KEEPFILTERS ( ISBLANK ( 'PO Daybook'[Customer Reference Number] ) ),
'PO Daybook'[__01. Production LT] > 49,
NOT ( ISBLANK ( 'PO Daybook'[12. Available Ship Date] ) )
)
VAR num =
SUMX ( summary, [cOrdUnits] )
VAR denom =
SUMX ( summary, [cOrdUnits] * 'PO Daybook'[__01. Product LT] )
RETURN
DIVIDE ( num, denom )Option 2
_X̄Production LT =
CALCULATE (
DIVIDE (
SUMX ( DISTINCT ( 'PO Daybook'[__01. Production LT] ), [01. Ordered Units] ),
SUMX (
DISTINCT ( 'PO Daybook'[PO Number] ),
[01. Ordered Units] * 'PO Daybook'[__01. Product LT]
)
),
KEEPFILTERS ( 'Product'[Newness Classification] <> "Collab" ),
KEEPFILTERS ( ISBLANK ( 'PO Daybook'[Customer Reference Number] ) ),
'PO Daybook'[__01. Production LT] > 49,
NOT ( ISBLANK ( 'PO Daybook'[12. Available Ship Date] ) )
)Pat
Hi Isnaan_Ahmed ,
It's hard to give too much advice on your DAX without knowing the model, but something that jumps out to me is the use of the SUMMARIZE function. It is not best practice to add extension columns using the SUMMARIZE function. It's best to wrap the SUMMARIZE function with ADDCOLUMNS.
Please take a look at this article describing the usage of ADDCOLUMNS and SUMMARIZE: