Match Closest Length to Existing Available Length Between Two Tables

[heiligbd]

So I have two tables. One with new sales orders and one with existing inventory. I want to match the existing inventory to the sales orders by both material # and the closest length that is longer than the requested length. I can't match a sales order to shorter existing inventory but anything that is equal to or longer is acceptable.  Below would be sample examples of tables and what I would like to match in yellow between the batches and the lengths. In the example, you can see that some orders would not have matching inventory available, but others would potentially have multiple available lengths that would work, but I want the closest to (equal or longer) to match to the ordered length by best fit. Therefore, if you had an order at 3 length, and on hand inventory at 3.5 and 4, I would want 3.5 to match to the order not 4.

Table 1
Sales Order #	Material #	Length Ordered
1	A	3
2	A	3.5
3	B	9
4	C	4
5	B	6.5
6	A	4.25
7	C	2

Thanks in advance for your help! 

Table 2
Material #	Batch	On Hand Length
A	11	4.35
A	22	2.95
A	33	4.15
B	44	8.5
B	55	6.75
C	66	4.25
C	77	2.25

Matched Table
Sales Order #	Material	Length Ordered	Matched Batch	On Hand Length
1	A	3	No Match	No Match
2	A	3.5	33	4.15
3	B	9	No Match	No Match
4	C	4	66	4.25
5	B	6.5	55	6.75
6	A	4.25	11	4.35
7	C	2	77	2.25

[DataInsights · Accepted solution]

@heiligbd,

Try these calculated columns in Table1:

Matched Batch = 
VAR vMaterial = Table1[Material #]
VAR vLengthOrdered = Table1[Length Ordered]
VAR vInventory =
    FILTER (
        Table2,
        Table2[Material #] = vMaterial
            && Table2[On Hand Length] >= vLengthOrdered
    )
VAR vMinOnHandLength =
    MINX ( vInventory, Table2[On Hand Length] )
VAR vMinBatch =
    MINX (
        FILTER ( vInventory, Table2[On Hand Length] = vMinOnHandLength ),
        Table2[Batch]
    )
VAR vResult =
    IF ( ISBLANK ( vMinBatch ), "No Match", CONVERT ( vMinBatch, STRING ) )
RETURN
    vResult

On Hand Length = 
VAR vMaterial = Table1[Material #]
VAR vLengthOrdered = Table1[Length Ordered]
VAR vInventory =
    FILTER (
        Table2,
        Table2[Material #] = vMaterial
            && Table2[On Hand Length] >= vLengthOrdered
    )
VAR vMinOnHandLength =
    MINX ( vInventory, Table2[On Hand Length] )
VAR vResult =
    IF ( ISBLANK ( vMinOnHandLength ), "No Match", CONVERT ( vMinOnHandLength, STRING ) )
RETURN
    vResult

[heiligbd · Accepted solution]

Awesome! Worked like a Champ! Thanks so much!

[DataInsights · Accepted solution]

@heiligbd,

Try these calculated columns in Table1:

Matched Batch = 
VAR vMaterial = Table1[Material #]
VAR vLengthOrdered = Table1[Length Ordered]
VAR vInventory =
    FILTER (
        Table2,
        Table2[Material #] = vMaterial
            && Table2[On Hand Length] >= vLengthOrdered
    )
VAR vMinOnHandLength =
    MINX ( vInventory, Table2[On Hand Length] )
VAR vMinBatch =
    MINX (
        FILTER ( vInventory, Table2[On Hand Length] = vMinOnHandLength ),
        Table2[Batch]
    )
VAR vResult =
    IF ( ISBLANK ( vMinBatch ), "No Match", CONVERT ( vMinBatch, STRING ) )
RETURN
    vResult

On Hand Length = 
VAR vMaterial = Table1[Material #]
VAR vLengthOrdered = Table1[Length Ordered]
VAR vInventory =
    FILTER (
        Table2,
        Table2[Material #] = vMaterial
            && Table2[On Hand Length] >= vLengthOrdered
    )
VAR vMinOnHandLength =
    MINX ( vInventory, Table2[On Hand Length] )
VAR vResult =
    IF ( ISBLANK ( vMinOnHandLength ), "No Match", CONVERT ( vMinOnHandLength, STRING ) )
RETURN
    vResult