Forum Discussion
Lookup Result Based on Range from Another Table
Hi All,
Need some help on a Calculated Column I am trying to create. I have two tables, one called 'sales', which is used in this DAX formula to calculate a sales order line level sales price (Column 'Line ASP'). The second, called 'Price List Updated', contains Item Numbers and multiple price "levels" defined by a min and mix (Columns 'Price Level', 'Lowest Price' and 'Highest Price' in that order).
Here is the 'sales' table, filtered to the issue I am referring to, where for some reason multiple results are returning null values, but others are not, even though the 'Line ASP' and 'ItemNumber' columns are the same.
| ItemNumber | InvoiceNumber | qty | $'s | Line ASP | Line Price Level |
| LF1923 | 4168922 | 6 | $2,280 | 380 | |
| LF1923 | 4167689 | 6 | $2,280 | 380 | |
| LF1923 | 4167659 | 6 | $2,280 | 380 | |
| LF1923 | 4167659 | 6 | $2,280 | 380 | |
| LF1923 | 4166738 | 6 | $2,280 | 380 | |
| LF1923 | 4151295 | 6 | $2,280 | 380 | |
| LF1923 | 4149280 | 6 | $2,280 | 380 | |
| LF1923 | 4148446 | 6 | $2,280 | 380 | Level 6 Price |
| LF1923 | 4108790 | 6 | $2,280 | 380 | Level 6 Price |
| LF1923 | 4423333 | 6 | $2,190 | 365 | Level 7 Price |
| LF1923 | 4423331 | 6 | $2,400 | 400 | |
| LF1923 | 4418252 | 6 | $2,280 | 380 | |
| LF1923 | 4406528 | 6 | $2,400 | 400 | Level 3 Price |
Here is my 'Price List Updated' table:
| Item Number | Price Level | Lowest Price | Highest Price |
| LF1923 | Level 10 Price | $335.73 | $353.4 |
| LF1923 | Level 9 Price | $353.4 | $360. |
| LF1923 | Level 8 Price | $360. | $365. |
| LF1923 | Level 7 Price | $365. | $380. |
| LF1923 | Level 6 Price | $380. | $385. |
| LF1923 | Level 5 Price | $385. | $390. |
| LF1923 | Level 4 Price | $390. | $400. |
| LF1923 | Level 3 Price | $400. | $415. |
| LF1923 | Level 2 Price | $415. | $430. |
| LF1923 | Level 1 Price | $430. |
My DAX is as follows. I wouldn't expect to see null values in my 'sales' table, given that it is "joined" on the Item Number and Level Price range. Any help would be appreciated!
CALCULATE (
VALUES ( 'Price List Updated'[Price Level] ),
FILTER (
'Price List Updated',
'Price List Updated'[Item Number] = 'sales'[ItemNumber]
&& NOT ISBLANK ( sales[Line ASP] )
&& sales[Line ASP] >= 'Price List Updated'[Lowest Price]
&& sales[Line ASP] < COALESCE ( 'Price List Updated'[Highest Price], 999999 )
)
)
Hi,
Could you please try the below for creating a calculated column?
Line Price Level = SUMMARIZE ( FILTER ( 'Price List Updated', 'Price List Updated'[Item Number] = 'sales'[ItemNumber] -- && NOT ISBLANK ( sales[Line ASP] ) && sales[Line ASP] >= 'Price List Updated'[Lowest Price] && sales[Line ASP] < COALESCE ( 'Price List Updated'[Highest Price], 999999 ) ), 'Price List Updated'[Price Level] )
1 Reply
Hi,
Could you please try the below for creating a calculated column?
Line Price Level = SUMMARIZE ( FILTER ( 'Price List Updated', 'Price List Updated'[Item Number] = 'sales'[ItemNumber] -- && NOT ISBLANK ( sales[Line ASP] ) && sales[Line ASP] >= 'Price List Updated'[Lowest Price] && sales[Line ASP] < COALESCE ( 'Price List Updated'[Highest Price], 999999 ) ), 'Price List Updated'[Price Level] )