Forum Discussion
Understanding Multiple Fact Tables
Greenwoodr v-frfei-msft I know this is old, but I have the same questions ...which approach to calculate the Sales for each "campaign" per customer
- bcdobbs4 years agoCommunity Champion
I'm not sure if this is the "best way" and may be considered bad practice.
However I normally make use of TREATAS in these cases to move the list of customers from one fact to another.
Something like:
CALCULATE(
[Your Measure],
TREATAS(VALUES( Campaign[CustomerId] ),
Customer[CustomerId]
))If anyone has a better suggestion would be interested.
- PaulDBrown4 years agoCommunity Champion
Here is one way. First the sample model:
With a SUM measure for sales, create the following:
Sales By Campaign & Customer = VAR StartDate = CALCULATE ( MIN ( 'Campaign Table'[Campaign Date] ), ALLEXCEPT ( 'Campaign Table', 'Campaign Table'[Campaign] ) ) VAR EndD = CALCULATE ( MAX ( 'Campaign Table'[Campaign Date] ), ALLEXCEPT ( 'Campaign Table', 'Campaign Table'[Campaign] ) ) RETURN CALCULATE ( [Sum Sales], CROSSFILTER ( 'Campaign Table'[Customer], 'Customer Table'[Customer Name], BOTH ), FILTER ( ALL ( 'Sales Table'[Sales Date] ), 'Sales Table'[Sales Date] >= StartDate && 'Sales Table'[Sales Date] <= EndD ) )to get:
I've attached the sample PBIX file
- VuongLM934 years agoHelper III
Thanks for the approach, my data for each customer, there is also specific "start date" and "end date" columns ( for example a 50% discount program apply for customer A , from 1/1/2021 to 20/1/2021 ) , how to calculate the sales for the campaign ?
- PaulDBrown4 years agoCommunity Champion
It would be helpful if you provided sample data in tabular form