How to improve performance issues for DAX Formula

[Isnaan_Ahmed]

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

[ppm1 · Accepted 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?

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

[besomebody20]

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