Forum Discussion

Isnaan_Ahmed's avatar
Isnaan_Ahmed
Frequent Visitor
3 years ago
Solved

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...
  • ppm1's avatar
    3 years ago

    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