Forum Discussion

KevSPHK's avatar
KevSPHK
Frequent Visitor
3 years ago

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...
Need Help
Tutorial Requests
wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
3 years ago

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])