Forum Discussion
Solved
Dax with Many to Many
I am looking to see effectivness of Specific Sales Promotions. These Promotions are 1 day pop ups at a store, think the knife guy at Costco. So i have a sales table with: Sales Person ID, Sales...
1.Create a Bridge Table (SalesPerson–Promotion pairs).
Relate it to both Sales and Promotions.
2.Use TREATAS to filter only salespeople in that promotion.
Sales_XDaysBefore =
CALCULATE(
SUM(Sales[SalesAmount]),
TREATAS(VALUES(PromotionParticipants[SalesPersonID]), Sales[SalesPersonID]),
DATESBETWEEN(
'Date'[Date],
MAX(Promotions[PromotionDate]) - X,
MAX(Promotions[PromotionDate]) - 1
)
)
Same pattern for After X Days (just shift the date range forward).Build measures for 30/60/90 days → Compare Before vs After → compute Lift %.
👉 Bridge + TREATAS is the key to handle many-to-many.
