Slow Summarize measure

[sabd80]

Hi,

I have below measure running very slow specialy for monthly and yearly period:

Constrained DIF MISS =

VAR NewTable=

FILTER(ADDCOLUMNS(

       SUMMARIZE('Sales Order Details', 'Sales Order Details'[Order Number]),

       "Qty",[Qty Ordered Running Total OrderNo]- [Qty Shipped Running Total OrderNo]

),[Constrained DIF Flag]="Yes"

)

VAR MaxOrder = maxx(NewTable, [Order Number])

return

sumx(FILTER(NewTable,'Sales Order Details'[Order Number]= MaxOrder), [Qty])

The orther measures used in above are:

Constrained DIF Flag =

IF (ISBLANK([Quantity Shipped]), BLANK(),

       IF([Qty Shipped Running Total OrderNo] <= SUM('Day 0 Business Plan'[Day 0 Business Plan - Cases]),

        "Yes",

        "No"

       )

)

Qty Ordered Running Total OrderNo =

CALCULATE([Quantity Ordered],

       FILTER(ALLSELECTED('Sales Order Details'), 'Sales Order Details'[Order Number]<= max('Sales Order Details'[Order Number]))

)

Qty Shipped Running Total OrderNo =

CALCULATE([Quantity Shipped],

       FILTER(ALLSELECTED('Sales Order Details'), 'Sales Order Details'[Order Number]<= max('Sales Order Details'[Order Number]))

)

I cann't provide the pbxi file.

Can it be optimized?

Thanks.

[sabd80]

Thanks @johnt75 , your dax return the correct order number, but the value of below

[Qty Ordered Running Total OrderNo] - [Qty Shipped Running Total OrderNo]

returns the value at the order number level, not the running total as in below screenshot.

I need to display this measure in a card visual, your measure will show 0, mine will show 388,635.

[johnt75]

Try changing it to

FILTER ( NewTable, 'Sales Order Details'[Order Number] <= MaxOrder ),

[sabd80]

Just a bit of correction.

that would show the cummulative value, that is not what I what want, I need the value of the difference up to the maximum order number.

[johnt75]

Try

Constrained DIF MISS =
VAR NewTable =
    FILTER (
        VALUES ( 'Sales Order Details'[Order Number] ),
        [Constrained DIF Flag] = "Yes"
    )
VAR MaxOrder =
    MAXX ( NewTable, [Order Number] )
RETURN
    SUMX (
        FILTER ( NewTable, 'Sales Order Details'[Order Number] = MaxOrder ),
        [Qty Ordered Running Total OrderNo] - [Qty Shipped Running Total OrderNo]
    )