Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 quantity is 14 return price $193.93. Since it didnt mean the requuirements of min qty 15 use min qty 10 and the associated price.
Sales Price Table
Cust | Item | UOM | Start date | Min Qty | Unit_Price |
38007 | 1000063 | BDL | 4/16/2021 | 5 | 203.15 |
38007 | 1000063 | BDL | 4/16/2021 | 10 | 193.93 |
38007 | 1000063 | BDL | 4/16/2021 | 15 | 190.4 |
VLE Table
Cust | Item | UOM | Qty |
38007 | 1000063 | BDL | 14 |
Solved! Go to Solution.
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] )
)
Here's one way to do this
Qty_Price =
VAR _ItemQty = SELECTEDVALUE ( VLE[Qty] )
VAR _MinQty = CALCULATE ( MIN ( SalesPrice[Min_Qty] ), SalesPrice[Min_Qty] >= _ItemQty )
RETURN
CALCULATE (
SELECTEDVALUE ( SalesPrice[Unit_Price] ),
SalesPrice[Min_Qty] = _MinQty
)
Note: This assumes the Cust, Item, and UOM columns in both tables are both filtered by the same dimension table(s). If they aren't related this way, then you'll need to propagate the filtering in the measure too.
For a couple of other methods, you may be interested in this similar question:
https://stackoverflow.com/questions/52525377/return-top-value-ordered-by-another-column
Hi Alexis!
Thank you! Can you show how the filtering would work?
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?
Here are my current relationships
The Sales_Code is Customer No.
I planned on building a measure to use in a visual
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] )
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |