PowerPivot DAX many-to-many relationship

Question

I have a PowerPivot model and I'm trying to retrieve the corresponding surcharge percent (as a function of VendorID and OrderQty) from a lookup table that contains several order quantity tiers and their associated surcharge percentages. Neither table contains unique values for any of the rows (it's a many-to-many case).

In other words, I'm trying to populate the "SurchargePct" column in the table below using the calculated column approach:

VendorID    OrderQty    SurchargePct
1               30             5%
1               20            10%
2               55            15%
2               80             3%

... based on the appropriate tier from the table below:

VendorID    Tier    MinQty  MaxQty  SurchargePct
1            A        15      24    10%
1            B        25      99    5%
2            A        40      59    15%
2            B        60      89    3%

Thanks!

Answer 1

Try this expression in the calculated column:

=
CALCULATE (
    MAX ( TableB[SurchargePct] ),
    FILTER (
        TableB,
        EARLIER ( TableA[OrderQty] ) >= [MinQty]
            && TableA[OrderQty] <= [MaxQty]
            && TableB[VendorID] = EARLIER ( TableA[VendorID] )
    )
)

Where TableA is your first table and TableB is the second.

Let me know if this helps.