Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Only Return Work Order Numbers After Amount Is Distributed

Good Day,   I am only wanting to return workorders where we didn't have enough quantityonhand to cover the order. In the example below quantityonhand in IM_ItemWarehouse is 139.  I need quantityon...
Amounts
DAX
Distribute
distribute amount
Filter
measure
johnt75's avatar
johnt75
Super User
3 years ago

You could try turning it into a calculated column instead of a measure, that way everything only needs to get calculated during data refresh

Shortfall =
VAR QuantityOnHand =
    RELATED ( 'IM_ItemWarehouse'[QuantityOnHand] )
VAR RequiredAmount = 'WO2_WorkOrderMaterialDetail'[QtyCommitted]
VAR CurrentIndex = 'WO2_WorkOrderMaterialDetail'[Index]
VAR PreviouslyUsed =
    CALCULATE (
        SUM ( 'WO2_WorkOrderMaterialDetail'[QtyCommitted] ),
        ALLEXCEPT (
            'WO2_WorkOrderMaterialDetail',
            'WO2_WorkOrderMaterialDetail'[ComponentItemNumber]
        ),
        'WO2_WorkOrderMaterialDetail'[Index] < CurrentIndex
    )
VAR AvailableStock =
    MAX ( QuantityOnHand - PreviouslyUsed, 0 )
RETURN
    MAX ( RequiredAmount - AvailableStock, 0 )
Anonymous's avatar
Anonymous
Not applicable
3 years ago

This doesnt work for me unfortunately, I keep getting an error saying the memory is insufficent to do it:/ Same as it was when I tried to put it in the visual. Thank you for all your help though, you did indeed give the right answer I just think my computer cannot handle this.