Forum Discussion
sabd80
Helper IV
Helper IVSlow Summarize measure
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.
4 Replies
johnt75Super User
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] )
sabd80Thanks 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.