Lookup based on Sum in Other Table & Certain criteras

[vipett]

I have two tables, one with Extra stock with certain attributes and one with my items. I try to find a suitable replacement article based on my list with "Extra stock".

The tables look roughly like this:

Extra stock (The tables Valid? in this example are all "Yes", but in real world they might be "No" as well

ItemNr	Attribute 1	Attribute 2	Attribute 3	A1 Valid?	A2 Valid?	A3 Valid?	S tock Qty	Combo
A	Red	Car	Solid	Yes	Yes	Yes	50	RedCarSolid
B	Blue	Moped	Transparent	Yes	Yes	Yes	500	BlueMopedTransparent
C	Blue	Moped	Transparent	Yes	Yes	Yes	100	BlueMopedTransparent

Items:

ItemNr	Attribute 1	Attribute 2	Attribute 3	Stock	Alternative ItemNr	Stock for Alternative	Combo
1	Red	Car	Solid	10			RedCarSolid
2	Blue	Moped	Transparent	20			BlueMopedTransparent
3	Orange	Moped	Transparent	30			OrangeMopedTransparent
4	Green	Car	Solid	40			GreenCarSolid

In the Items table, I want to display A as an alternative ItemNr for 1 with 50 in stock and for 2 I want B to be chosen (and when that stock is reduced below 100 then C should be the Alternative ItemNr.

There will be multiple combinations of the different attributes that don't have any stock.

When I try with Lookupvalue = 'Extra Stock'[ItemNr],[Stock quantity],Calculate(Max('Extra Stock'[Stock Quantity],_*My filters here*), Combo,Combo) I get "A single value for column Combo in Extra stock cannot be determined. [....].

What is the best way of doing this?

[v-yangliu-msft · Accepted solution]

Hi  @vipett ,

Here are the steps you can follow：

1. Create calculated column.

Alternative ItemNr =
VAR _Item =
    MAXX (
        FILTER (
            ALL ( 'Extra stock' ),
            'Extra stock'[Attribute 1] = EARLIER ( 'Items'[Attribute 1] )
                && 'Extra stock'[Attribute 2] = EARLIER ( 'Items'[Attribute 2] )
                && 'Extra stock'[Attribute 3] = EARLIER ( 'Items'[Attribute 3] )
                && 'Extra stock'[Stock Qty]
                    = MAXX (
                        FILTER (
                            ALL ( 'Extra stock' ),
                            'Extra stock'[Attribute 1] = EARLIER ( 'Extra stock'[Attribute 1] )
                                && 'Extra stock'[Attribute 2] = EARLIER ( 'Extra stock'[Attribute 2] )
                                && 'Extra stock'[Attribute 3] = EARLIER ( 'Extra stock'[Attribute 3] )
                        ),
                        'Extra stock'[Stock Qty]
                    )
        ),
        'Extra stock'[ItemNr]
    )
RETURN
    IF (
        _Item = BLANK ()
            && 'Items'[Stock] <= 100,
        MAXX (
            FILTER ( ALL ( 'Extra stock' ), 'Extra stock'[Stock Qty] = 100 ),
            'Extra stock'[ItemNr]
        ),
        _Item
    )

Stock for Alternative =
VAR _Item =
    MAXX (
        FILTER (
            ALL ( 'Extra stock' ),
            'Extra stock'[Attribute 1] = EARLIER ( 'Items'[Attribute 1] )
                && 'Extra stock'[Attribute 2] = EARLIER ( 'Items'[Attribute 2] )
                && 'Extra stock'[Attribute 3] = EARLIER ( 'Items'[Attribute 3] )
                && 'Extra stock'[Stock Qty]
                    = MAXX (
                        FILTER (
                            ALL ( 'Extra stock' ),
                            'Extra stock'[Attribute 1] = EARLIER ( 'Extra stock'[Attribute 1] )
                                && 'Extra stock'[Attribute 2] = EARLIER ( 'Extra stock'[Attribute 2] )
                                && 'Extra stock'[Attribute 3] = EARLIER ( 'Extra stock'[Attribute 3] )
                        ),
                        'Extra stock'[Stock Qty]
                    )
        ),
        'Extra stock'[Stock Qty]
    )
RETURN
    IF (
        _Item = BLANK ()
            && 'Items'[Stock] <= 100,
        MAXX (
            FILTER ( ALL ( 'Extra stock' ), 'Extra stock'[Stock Qty] = 100 ),
            'Extra stock'[Stock Qty]
        ),
        _Item
    )

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly