Forum Discussion
DAX/ M-language help - Returning a result, when a value fall into a number range in the data
Hello,
my goal is to create a new column of "Order Unit Price" in power query or power bi table, which the "Order Unit Price" is depends on "Product" & "Vendor" & "Order quantity" relating to SOURCE table.
May I ask how to write the syntex in Power BI to look up the order unit price from SOURCE TABLE? (sample structure are provided below) Many thanks
My Goal
| Product | Vendor | Order Quantity | Order Unit Price |
| A | Cat | 22 | ?? |
| B | Bat | 45 | ?? |
Source Table
| Product | Vendor | Price Level | Minimum Quantity | Unit Price |
| A | Cat | Px Lev 1 | 1 | 10.00 |
| A | Cat | Px Lev 2 | 21 | 8.00 |
| A | Cat | Px Lev 3 | 41 | 7.00 |
| A | Bat | Px Lev 1 | 1 | 10.00 |
| A | Bat | Px Lev 2 | 31 | 8.50 |
| A | Bat | Px Lev 3 | 46 | 7.10 |
| B | Cat | Px Lev 1 | 1 | 8.00 |
| B | Cat | Px Lev 2 | 21 | 6.00 |
| B | Cat | Px Lev 3 | 41 | 5.00 |
| B | Bat | Px Lev 1 | 1 | 8.00 |
| B | Bat | Px Lev 2 | 31 | 6.50 |
| B | Bat | Px Lev 3 | 46 | 5.10 |
2 Replies
M code
NewStep=let a=Table.Buffer(Table.Group(SourceTable,{"Product","Vendor"},{"n",each Table.Sort(_,{"Minimum Quantity",1})})) in Table.AddColumn(YourOrderList,"OrderUnitPrice",each let b=a{[Product=[Product],Vendor=[Vendor]]}? in if b=null then null else Table.Skip(b,(x)=>x[Minimum Quantity]>[Order Quantity]){0}?[Unit Price]?)
Dax code
if there are no relationships between those tables, this code can create a calculated column in table Order
Unit Price Column=MAXX(TOPN(1,FILTER(SourceTable,SourceTable[Product]=OrderTable[Product]&&SourceTable[Vendor]=OrderTable[Vendor]&&SourceTable[Minimum Quantity]<=OrderTable[Order Quantity]),SourceTable[Minimum Quantity]),SourceTable[Unit Price])
Hi wdx223_Daniel
Thanks for your answer, the DAX code does work well,
however the M code shows error. Can you help futher on this.
I have put a PBix file showing error in below
https://drive.google.com/drive/folders/1_xT1nCE49RMIVUiwmj5zM7NB5-hKVm39?usp=share_linkhttps://drive.google.com/file/d/1Qe2nit0W9rj4l2O18QbDYx_tQovACGpv/view?usp=share_linkMany Thanks
