Lookup on different tables by unique concatenated search criteria

[Hyperchef1969]

Hi,

I have two tables, one is the inventory table, the other is the transaction table. See below printscreen. What I am trying to achieve is the following: In the on hand table I want the comment to be displayed from the material transaction table (value column K displayed in column E). I want a lookup from the combination article number and subinventory from the on hand table, to the article and subinventory from the material transaction table. This my search criteria (in excel I build it in column D and H). To be displayed in the inventory table (column E) should be the comment of the maximum transaction date (column I) in relation to my search criteria. How should I achieve this by Power BI and dax? Step by step please. Many thanks in advance for your help!

[v-jianboli-msft · Accepted solution]

Hi @Hyperchef1969 ,

Please try to add a calculated column in the Inventory table:

Comment =
VAR _a =
    MAXX (
        FILTER (
            'Transaction',
            [Search criteria] = EARLIER ( Inventory[Search criteria] )
        ),
        [Transaction date]
    )
RETURN
    CALCULATE (
        MAX ( 'Transaction'[Comment] ),
        FILTER (
            'Transaction',
            [Search criteria] = EARLIER ( 'Inventory'[Search criteria] )
                && [Transaction date] = _a
        )
    )

Final output:

Best Regards,

Jianbo Li

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

[Bifinity_75]

Hi @Hyperchef1969 , try this:

- Create a calculate column in your transaction table:

Search_Criteria2 = IF (CALCULATE(max('Transaction table'[Transaction date]),
ALLEXCEPT('Transaction table','Transaction table'[Search_Criteria]))='Transaction table'[Transaction date],
'Transaction table'[Search_Criteria],"xxx")

- And in your Inventory table, create this calculate column:

Comment__ = 
maxx(filter('Transaction table' , 
        search('Transaction table'[Search_Criteria2],'Inventory Table'[Search Criteria],,0)>0),
        'Transaction table'[Comment])

Best Regards