Forum Discussion
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 Date, Product ID, Sales Amount
I also have a Sales Promotion Table with: Promotion Name, Sales Promotion Date, ID of Sales Person who Participated.
Also have a Date table, Product Table, etc.
What i am trying to get is the effectivness of each promotion. I want to see sales of the Participating Sales People 30, 60, 90 days before and after the event. THe issues is the same salesperson does multiple promotions so i can't link on Sales Person ID. I need a bridge table and some filtering with the dax to get what i need.
Any help would be appreciated.
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.
13 Replies
One question and one request
question: each promotion lasts only one day and is done by a single person?
request
Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.
Need help uploading data? click here
Want faster answers? click here
Each Promotion only lasts 1 day, but can be done at multiple locations by multiple salespeople. Don't Care about the locations, just did the promotion work.
Thanks, can you Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.
Need help uploading data? click here
- Want faster answers? click here
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.
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?
Hi JimSales81 ,
Thank you Ashish_Mathur and FBergamaschi for the response provided!
Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Thank you for your understanding!
Best Regards,
Tejaswi.
Community SupportHi 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.
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.
