Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
Isnaan_Ahmed
Frequent Visitor

How to improve performance issues for DAX Formula

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:

 

 

_X̄Production LT = // = SUMPRODUCT ( PO order qty, Lead time) over total Ordered qty


VAR _FilteredTable =        // First create a table with all the filters required:
CALCULATETABLE(
    'PO Daybook',
        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 _NewTotalqty =      // Calculating Total Ordered qty from filtered table
CALCULATE (
    sumx (
        SUMMARIZE (
            _FilteredTable,
            'PO Daybook'[PO Number],
            "Qty",
            [01. Ordered Units] ),
            [Qty] )
)

VAR _NewSumProdLTandQty =       // sum product calculation of LT and PO qty, where LT is a calculated column that needs to be added
sumx (
    SUMMARIZE (
    _FilteredTable,
    'PO Daybook'[__01. Production LT],
    "qty",
    [01. Ordered Units]
    ),
    [qty] * 'PO Daybook'[__01. Production LT]
)

Return     // Weighted average LT
DIVIDE(
    _NewSumProdLTandQty,
    _NewTotalqty
    )
 
 
My big problem is that this is taking a lot of memory and is taking a long time for some of my visuals to load. I have filters on all pages applied also, in addition to the filters in the DAX formula. The visuals take up to 19706 ms based on the performance analyzer, and when published sometimes doesn't load at all. My DAX was written so that it could work and I get the results I want, but I'm not good enough to optimise it from here. 
 
My question is, can I improve the DAX code to make it run more efficiently? Where are the issues stemming from, and are there any workarounds?
 
Thank you in advance,
IA
1 ACCEPTED SOLUTION
ppm1
Solution Sage
Solution Sage

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 ( numdenom )

 

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
Microsoft Employee

View solution in original post

2 REPLIES 2
ppm1
Solution Sage
Solution Sage

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 ( numdenom )

 

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
Microsoft Employee
besomebody20
Resolver I
Resolver I

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:

Best practices using SUMMARIZE and ADDCOLUMNS - SQLBI

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.