Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I have a report I am looking to publish, however certain visuals are prooving to have some performance issues, mainly due to a DAX measure they all rely on. This is the lowdown of my issue:
1) I have a data model with a live connection (SASS), which has a fact table with about 370,000 lines (orders). I am attempting to calculate the lead time (date difference between 2 columns) for each line where the order line meets certain filter requirements.
2) From here I am attempting to calculate the weighted average lead time over time (ordered units * Lead time / total ordered units).
To do this, I am using the below DAX to filter the data model, and get an average weighted lead time that can be displayed over time, by supplier, by country etec. The DAX formula for this is below:
Solved! Go to Solution.
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] ) )
)
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] ) )
)
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:
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |