Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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:
User | Count |
---|---|
14 | |
9 | |
7 | |
7 | |
6 |
User | Count |
---|---|
21 | |
11 | |
10 | |
10 | |
8 |