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.

vojtechsima's avatar
vojtechsima
Super User
1 year ago

Hey, JimSales81 ,

I have example from my earlier doing.

Try to build something like that

 

And then basic measure for (in my case count):

count vulnerabilities fix relationship = 
CALCULATE(
    COUNTROWS(_case_4_fact_vulnerabilities),
    TREATAS( VALUES(_case_4_briding_device_app[device_id]), _case_4_fact_vulnerabilities[device_id])
)

Just modify it to yours, this is general approach how to get Many To Many to One To Many with nice DAX.

Use then this basic measure as base for any other measures you wanna do.