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
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?
_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] ) )
)