Multiple many-to-many relationships

Your task is to start from a filter on Promotion and bring it to Sales. Following the many-to-many pattern, you might have tried this solution:

Sales Amount = 
CALCULATE (
    SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] ), 
    CALCULATETABLE ( 'Product', BridgePromotionProduct ),
    CALCULATETABLE ( 'Date', BridgePromotionProduct )
)

Unfortunately, as simple as it is, this formula does not work. In fact, it filters separately products and dates. If a product was on sale for 10 days and another product was on sale for only 5 days, the result will be wrong, because it will report sales of a product when the other one was on sale.

What you have to do is filter the pairs of product and date at the same time. In this case, the theory of expanded table becomes useful. In fact, the expanded bridge table contains both date and product, in the same table. Thus, the correct solution is actually easier than expected:

Sales Amount = 
CALCULATE (
    SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] ), 
    BridgePromotionProduct
)

Unfortunately, this measure works fine but it is utterly slow, resulting in frustration when used in a report. Another possible solution requires you to create a smaller table that, instead of being directly related to the date, stores start and end date of promotion for each product. If there are no blackout dates in the periods of promotions for a product, you can create a calculated table with this code:

ProductsInPromotion = 
ADDCOLUMNS (
    SUMMARIZE (
        BridgePromotionProduct,
        BridgePromotionProduct[PromotionKey],
        BridgePromotionProduct[ProductKey]
    ),
    "FromDate", LOOKUPVALUE (
        'Date'[Date],
        'Date'[DateKey], CALCULATE ( MIN ( BridgePromotionProduct[Order DateKey] ) )
    ),
    "ToDate", LOOKUPVALUE (
        'Date'[Date],
        'Date'[DateKey], CALCULATE ( MAX ( BridgePromotionProduct[Order DateKey] ) )
    )
)

If there are blackout dates, then you have to revert to some ETL code, as DAX is no longer a viable option. Once the table is in place, you can easily compute the Sales Amount with this code:

[Sales Amount] = 
SUMX (
    ProductsInPromotion,
    CALCULATE (
        SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] ),
        DATESBETWEEN ( 'Date'[Date], ProductsInPromotion[FromDate], ProductsInPromotion[ToDate] )
    )
)

This measure works fine if you select a single promotion but , as soon as you select many of them, it loses the many-to-many behavior of non-additivity and shows as a result the sum of all promotions, resulting in a number which might be interpreted in the wrong way. On the other hand, it is extremely faster than the previous one.

Download