Forum Discussion

Greenwoodr's avatar
Greenwoodr
Helper I
7 years ago

Understanding Multiple Fact Tables

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

 

Need Help

13 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    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

    • bcdobbs's avatar
      bcdobbs
      Community Champion
      4 years ago

      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.

       

       

       

       

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      4 years ago

      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's avatar
        VuongLM93
        Helper III
        4 years ago

        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 ?