Understanding Multiple Fact Tables

[Greenwoodr]

Hi

I would be grateful for some assitance with the following data model design query.......

I have the following structure 

1) Fact Table 1 Sales :(1 row per sale) : Contains details of sales with links to date , customer and product dimensions  (amongst others in tradditional star schema model)

2) Fact Table 2 Campaigns: (1 row per customer per campaign sent) Contains details of marketing campaigns received by a customer  with links to the customer dimension and a campaigns dimension sumarising the campaigns details (each campaign being split into diffferent treatments)

I would like to get the sales results (How many people were mailed, how many responded etc) for specific campaigns but am struggling to work out how I do this and what changes are needed to the design to allow me to achieve this. Having read this it seems to be called the chasm trap 

For reference I can calculate the answer in SQL by pulling out who was mailed in each campaign and searching for the relevant sales in the sales table. I could import this into PowerBI and report of that but it would mean sales data existing in both Fact Tables and have a feeling that there is a better way?

Does anyone have any suggestions as to the best way to approach this?

Many Thanks 

Richard

[VuongLM93]

@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

[PaulDBrown]

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

[VuongLM93]

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 ?

[PaulDBrown]

It would be helpful if you provided sample data in tabular form

[VuongLM93]

https://docs.google.com/spreadsheets/d/1g28W34c8Z9MAgbdAYwyveYIiABKjwekD/edit#gid=1517233207

Above is a sample data , which I made up for explaining my case,

[PaulDBrown]

See if this works, though the performance is slow:

Sales By Campaign & Customer =
VAR StartDate =
    CALCULATE (
        MAX ( 'Campaign Table'[Start Date] ),
        ALLEXCEPT ( 'Campaign Table', 'Campaign Table'[CampaignID] )
    )
VAR StartKey =
    INT ( DAY ( StartDate ) & MONTH ( StartDate ) & YEAR ( StartDate ) )
VAR EndDate =
    CALCULATE (
        MAX ( 'Campaign Table'[End Date] ),
        ALLEXCEPT ( 'Campaign Table', 'Campaign Table'[CampaignID] )
    )
VAR EndKey =
    INT ( DAY ( EndDate ) & MONTH ( EndDate ) & YEAR ( EndDate ) )
RETURN
    CALCULATE (
        [Sum Sales],
        CROSSFILTER ( 'Campaign Table'[Customer Code], 'Customer Table'[Customer Code], BOTH ),
        FILTER (
            ALL ( 'Sales Table'[DateKey] ),
            'Sales Table'[DateKey] >= StartKey
                && 'Sales Table'[DateKey] <= EndKey
        )
    )

I've attached the sample PBIX file

[Ashish_Mathur]

Hi,

My approach would be to write a calculated column formula in the Campaign Table to get the Amount from the Fact- Sales Orders Table.  For this we will have to first convert the DateKey column into an actual date column (the way dates are in the Campaign table).  Once we have the Amount column in the Campaign table, we just have to write a simple SUM measure.

Are you OK with this approach. 

[VuongLM93]

Hi @Ashish_Mathur  , how to create this calculated column  to get the according amount from the Sales table ? since there is 2 dates column , in Excel I would use SumIfs to calcualte the sales mount between the Start date and End Date of the campaign; would be helpful if you could help write an example

[Ashish_Mathur]

Hi,

You may download the PBI file from here.

Hope this helps.

[VuongLM93]

@Ashish_Mathur  for the Fact-Sales table I cannot modify since it's in a Direct Query 😞

can I create calculated column (since the datekey is related to the calendar date table ) ?

[Ashish_Mathur]

Hi,

I am not aware of any other way.

[bcdobbs]

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.

[v-frfei-msft]

Hi @Greenwoodr,

Could you please share your sample data to me? You can upload your file to one drive and share the link here.

Regards,

Frank