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.
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
