Mapping Trouble with One to Many Relationship

[defisher]

I have a purchase table that contains a tax1 and tax2 field for each purchase of a specific item. This table is the only source for the tax1 and tax2 fields.
I also have a lookup table that contains the base price of each item.
I have these tables connected in a one to many relationship on an "item id" field.
I want to do some kind calculated column/measure in my lookup table such as "tax1_lookup = calculate(min(tax1), purchases.item_id = lookup.item_id)". The idea is I just want to grab of the tax values from the purchases table so I can move that field to my lookup table. Perferably, I would like to get the tax value for the most recent purchase (using purchase date) for each serial number, but just getting any of the tax values would be good enough to start.

[v-yulgu-msft · Accepted solution]

Hi @defisher,

Add a calculated column in purchase table.

most recent tax1 =
CALCULATE (
    MAX ( Purchase[tax1] ),
    FILTER (
        ALLEXCEPT ( Purchase, Purchase[Itemid] ),
        Purchase[Date]
            = CALCULATE ( MAX ( Purchase[Date] ), ALLEXCEPT ( Purchase, Purchase[Itemid] ) )
    )
)

Add a calculated column in LookUp table.

Tax1 = LOOKUPVALUE(Purchase[most recent tax1],Purchase[Itemid],Lookup[Itemid])

Best regards,

Yuliana Gu