cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Isnaan_Ahmed
Regular 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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors