Many-to-Many (M2M) relationship in PowerPivot. And Multiple M2M’s!

Here and here are two examples to solve 1-step M2M relationship in PowerPivot using DAX language.

Multiple M2M in PowerPivot: To solve multiple M2M >> use nested Calculate multiple times, each time for 1-step M2M.

Example:
Promotion – Product – Order:
Order is M2M with Product. This necessitates another table to materialize this M2M relationship. Let’s call it: OrderProductM2M.
Product is M2M with Promotion. Likewise to materialize this relationship, let’s call the new table PromotionProductM2M.

To know the count of orders that a promotion triggered, using DAX:

CALCULATE(  CALCULATE(  COUNTROWS(
Order),Product, OrderProductM2M  ),Promotion,PromotionProductM2M  )

Done!

Leave a Reply

Your email address will not be published. Required fields are marked *