Forum Discussion

JimSales81's avatar
JimSales81
Helper I
1 year ago
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...
Need Help
  • Shahid12523's avatar
    Shahid12523
    1 year ago

    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.

v-tejrama's avatar
v-tejrama
Community Support
1 year ago

Hi JimSales81 ,

 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you,

Tejaswi.

v-tejrama's avatar
v-tejrama
Community Support
11 months ago

Hi  JimSales81 ,


I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

 

Thank you.