How to get the value by price?

[Sport_MACMUS]

Hi guys,

I'd like to get a discount value by price, I only know how to get the latest value, but I don't know how to get the value by different prices.

I'd like to get the 10% discount if apple's price is between 101 to 200, and the 15% discount if banana's price is between 201 to 300, the table looks as below：

How should I express it? Thanks.

[TeeSee1 · Accepted solution]

You can try

SELECT(
 discount table[Discount]
 AND(
  [Item] = {_THISROW].[Item],
  [Price1] <= [_THISROW].[Price],
  [_THISROW].[Price] <= [Price2]
 )
)

This assumes that you have a table where you have {Item], [Price] and a [Discount] column, either virtual or stored, with the above expression as its Formula.

[Sport_MACMUS]

Thanks,  TeeSeee1

It works! Thank you!