Lookup Result Based on Range from Another Table

[tkfisher]

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!

Line Price Level =
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 )
    )
)

[Jihwan_Kim · Accepted solution]

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