Forum Discussion
Dax with Many to Many
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.
Hi,
PBI file attached.
Hope this helps.
This works to a degree, but in this example If i look at the 5/17/2025 BOGO Event. The 30 days later should add up to 15,823. It is showing 21,897 because that is the 30 days later for all the salespeople. not just the event participants. How can we show the total for the event participants only. Ideally, not listing their names just the total for hte people who did the sales event.
That should be possible with the SUMX() function. So you want a card visual (not a table). Am i right?