Forum Discussion
Lookup nearest match on multiple columns same table
Hello Power BI fanatics,
i have this challenge and i can't get my head around on how to solve it. Preferably the solution i'm looking for would be Power Query, but Dax would also be much appreciated.
The table below shows several part id's with an required quantity next to it. I would like to add another column which is the price i would be paying. The price is graduated. If the Qty = 1 than it should return the Default Price. Any other Qty > 1 should be evaluated against the QuantityBreaks and find the closest match. Then multiply the quantity agains the related Unitprice.
Does anybody have an idea on how to do this?
Kind regards,
Jeroen
Try this calculated column (DAX):
Price = VAR vQty = Parts[Qty] VAR vPriceToUse = SWITCH ( TRUE (), vQty >= Parts[QuantityBreak3], Parts[UnitPrice3], vQty >= Parts[QuantityBreak2], Parts[UnitPrice2], vQty >= Parts[QuantityBreak1], Parts[UnitPrice1], Parts[DefaultPrice] ) VAR vResult = vQty * vPriceToUse RETURN vResultyou can try to use DAX to create a new column
Column = VAR qb1=ABS('Table'[Qty]-'Table'[QuantityBreak1]) VAR qb2=ABS('Table'[Qty]-'Table'[QuantityBreak2]) VAR qb3=ABS('Table'[Qty]-'Table'[Quantitybreak3]) VAR result=min(min(qb1,qb2),qb3) return if('Table'[Qty]=1,'Table'[DefaultPrice],if(result=qb1,'Table'[Unitprice1],if(result=qb2,'Table'[Unitprice2],'Table'[Unitprice3])))please see the attachment below.
You can use the ISBLANK function to ignore blanks:
Price = VAR vQty = Parts[Qty] VAR vPriceToUse = SWITCH ( TRUE (), vQty >= Parts[QuantityBreak3] && NOT ISBLANK ( Parts[QuantityBreak3] ), Parts[UnitPrice3], vQty >= Parts[QuantityBreak2] && NOT ISBLANK ( Parts[QuantityBreak2] ), Parts[UnitPrice2], vQty >= Parts[QuantityBreak1] && NOT ISBLANK ( Parts[QuantityBreak1] ), Parts[UnitPrice1], Parts[DefaultPrice] ) VAR vResult = vQty * vPriceToUse RETURN vResultHere's the Power Query version:
if [Qty] >= [QuantityBreak3] and [QuantityBreak3] <> null then [Qty] * [UnitPrice3] else if [Qty] >= [QuantityBreak2] and [QuantityBreak2] <> null then [Qty] * [UnitPrice2] else if [Qty] >= [QuantityBreak1] and [QuantityBreak1] <> null then [Qty] * [UnitPrice1] else [Qty] * [DefaultPrice]
8 Replies
Try this calculated column (DAX):
Price = VAR vQty = Parts[Qty] VAR vPriceToUse = SWITCH ( TRUE (), vQty >= Parts[QuantityBreak3], Parts[UnitPrice3], vQty >= Parts[QuantityBreak2], Parts[UnitPrice2], vQty >= Parts[QuantityBreak1], Parts[UnitPrice1], Parts[DefaultPrice] ) VAR vResult = vQty * vPriceToUse RETURN vResultyou can try to use DAX to create a new column
Column = VAR qb1=ABS('Table'[Qty]-'Table'[QuantityBreak1]) VAR qb2=ABS('Table'[Qty]-'Table'[QuantityBreak2]) VAR qb3=ABS('Table'[Qty]-'Table'[Quantitybreak3]) VAR result=min(min(qb1,qb2),qb3) return if('Table'[Qty]=1,'Table'[DefaultPrice],if(result=qb1,'Table'[Unitprice1],if(result=qb2,'Table'[Unitprice2],'Table'[Unitprice3])))please see the attachment below.
DataInsights & ryan_mayu thank you so much. You are life savers! Cool to see there's more than one way to do it! Thanks again! Now i'm back into the books to see what you actually did:-)
This works like a charm. What i forgot to mention is that Quantity breaks and Unit prices are not necessarily filled. Does anyone know how to ignore blanks in the Switch function?
You can use the ISBLANK function to ignore blanks:
Price = VAR vQty = Parts[Qty] VAR vPriceToUse = SWITCH ( TRUE (), vQty >= Parts[QuantityBreak3] && NOT ISBLANK ( Parts[QuantityBreak3] ), Parts[UnitPrice3], vQty >= Parts[QuantityBreak2] && NOT ISBLANK ( Parts[QuantityBreak2] ), Parts[UnitPrice2], vQty >= Parts[QuantityBreak1] && NOT ISBLANK ( Parts[QuantityBreak1] ), Parts[UnitPrice1], Parts[DefaultPrice] ) VAR vResult = vQty * vPriceToUse RETURN vResultThis is also a great solution. I can either take care of it in Dax or M now! Thanks for reaching the helping hand!
Here's the Power Query version:
if [Qty] >= [QuantityBreak3] and [QuantityBreak3] <> null then [Qty] * [UnitPrice3] else if [Qty] >= [QuantityBreak2] and [QuantityBreak2] <> null then [Qty] * [UnitPrice2] else if [Qty] >= [QuantityBreak1] and [QuantityBreak1] <> null then [Qty] * [UnitPrice1] else [Qty] * [DefaultPrice]Thanks DataInsights this works perfectly! Didn't think of categorizing it.
