Forum Discussion
nok
Advocate II
Advocate IIFind the nearest and highest value by ID
I have two tables that follow this structure:
Order
| ID | Qty |
| 111 | 1000 |
| 222 | 4500 |
Scale
| ID | ScaleQty |
| 111 | 1000 |
| 111 | 2000 |
| 111 | 5000 |
| 111 | 10000 |
| 222 | 3500 |
| 222 | 6000 |
| 222 | 9000 |
I'd like to create a new column in the Order table that, for each ID, checks the quantity ordered (in the Order[Qty] column) and finds the nearest and highest quantity in the Scale[ScaleQty] column. It can't be the largest Qty of them all (for example ID 111, it can't be 10000); so for ID 111 it needs to be the nearest and highest quantity to 1000, which in this case would be 2000.
The final result of this new column would be something like this:
| ID | Qty | NewColumn |
| 111 | 1000 | 2000 |
| 222 | 4500 | 6000 |
How can I do this?
Hi,
This calcualated column formula in the Orders table will work
=CALCULATE(MIN(Scale[ScaleQty]),FILTER(Scale,Scale[ID]=EARLIER('Order'[ID])&&[ScaleQty]>EARLIER('Order'[Qty])))Hope this helps.
2 Replies
- Ahmedx
Super User
pls try
- Ashish_Mathur
Hi,
This calcualated column formula in the Orders table will work
=CALCULATE(MIN(Scale[ScaleQty]),FILTER(Scale,Scale[ID]=EARLIER('Order'[ID])&&[ScaleQty]>EARLIER('Order'[Qty])))Hope this helps.
