Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Referencing a Min Quantity

I am trying to Reference a Minimum Quantity field when looking at actual Quantities on a Vendor Ledger Entries Table    For Example: If Customer is 39007 and item is 1000063 and Unit is BDL and qu...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    OK, then I'd assume [Cust] doesn't need to be propagated since the customer dimension should be filtering them both the same and we just need to apply the Item and UOM filtering from VLE to SalesPrice.

     

    Try this

    Qty_Price =
VAR _ItemQty = SELECTEDVALUE ( VLE[Qty] )
VAR _Item = SELECTEDVALUE ( VLE[Item] )
VAR _UOM = SELECTEDVALUE ( VLE[UOM] )
VAR _MinQty =
    CALCULATE (
        MIN ( SalesPrice[Min_Qty] ),
        SalesPrice[Min_Qty] >= _ItemQty,
        SalesPrice[Item] = _Item,
        SalesPrice[UOM] = _UOM
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( SalesPrice[Unit_Price] ),
        SalesPrice[Min_Qty] = _MinQty,
        SalesPrice[Item] = _Item,
        SalesPrice[UOM] = _UOM
    )

    Or you could also use TREATAS:

    Qty_Price =
VAR _ItemQty = SELECTEDVALUE ( VLE[Qty] )
VAR _MinQty =
    CALCULATE (
        MIN ( SalesPrice[Min_Qty] ),
        SalesPrice[Min_Qty] >= _ItemQty,
        TREATAS ( VALUES ( VLE[Item] ), SalesPrice[Item] ),
        TREATAS ( VALUES ( VLE[UOM] ), SalesPrice[UOM] )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( SalesPrice[Unit_Price] ),
        SalesPrice[Min_Qty] = _MinQty,
        TREATAS ( VALUES ( VLE[Item] ), SalesPrice[Item] ),
        TREATAS ( VALUES ( VLE[UOM] ), SalesPrice[UOM] )
    )

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Alexis!

Thank you! Can you show how the filtering would work?

AlexisOlson's avatar
AlexisOlson
Super User
3 years ago

It depends on how your table relationships and evaluation context are set up. What does your relationship diagram look like? Are you building a measure to use in a visual or a calculated column?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Here are my current relationships 

     

    The Sales_Code is Customer No.

     

     

    I planned on building a measure to use in a visual 

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      3 years ago

      OK, then I'd assume [Cust] doesn't need to be propagated since the customer dimension should be filtering them both the same and we just need to apply the Item and UOM filtering from VLE to SalesPrice.

       

      Try this

      Qty_Price =
VAR _ItemQty = SELECTEDVALUE ( VLE[Qty] )
VAR _Item = SELECTEDVALUE ( VLE[Item] )
VAR _UOM = SELECTEDVALUE ( VLE[UOM] )
VAR _MinQty =
    CALCULATE (
        MIN ( SalesPrice[Min_Qty] ),
        SalesPrice[Min_Qty] >= _ItemQty,
        SalesPrice[Item] = _Item,
        SalesPrice[UOM] = _UOM
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( SalesPrice[Unit_Price] ),
        SalesPrice[Min_Qty] = _MinQty,
        SalesPrice[Item] = _Item,
        SalesPrice[UOM] = _UOM
    )

      Or you could also use TREATAS:

      Qty_Price =
VAR _ItemQty = SELECTEDVALUE ( VLE[Qty] )
VAR _MinQty =
    CALCULATE (
        MIN ( SalesPrice[Min_Qty] ),
        SalesPrice[Min_Qty] >= _ItemQty,
        TREATAS ( VALUES ( VLE[Item] ), SalesPrice[Item] ),
        TREATAS ( VALUES ( VLE[UOM] ), SalesPrice[UOM] )
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( SalesPrice[Unit_Price] ),
        SalesPrice[Min_Qty] = _MinQty,
        TREATAS ( VALUES ( VLE[Item] ), SalesPrice[Item] ),
        TREATAS ( VALUES ( VLE[UOM] ), SalesPrice[UOM] )
    )