Power BI model

[ConstMoss]

 Hello all – I have the following tables.

1. Sale
2. Customer
3. Channel
4. Marketing Campaign

1. Customer is related to Sales on CustomerID.
2. Channel is the means by which a customer get to the Sales (friend, search, etc.). Customer is related to Chanel on CustomerID. Each customer can come through multiple channels until they purchase something.
3. Marketing Campaign is similar to Channel.  Customer is related to Marketing Campaign on CustomerID.

We’d like to track sales by channel and Marketing Campaign but unable to get the data model to work – it doesn’t let us setup bidirectional filter on some joins and when it does, it won’t let us join some tables. Any idea how to setup the data model in this scenario?

Thanks in advance!

[CheenuSing]

Hi @ConstMoss

Can you share the data model and sample data.

Cheers

CheenuSing

[ConstMoss]

Is there a way to upload files directly? Here is the sample file - https://ufile.io/a714a

[v-huizhn-msft]

Hi @ConstMoss,

I download the file successfuly, do you want to track all channels and canpains for each customer? What do the result format look like? Could you please share a picture or table to list the expected result? Thanks a lot.

Best Regards,
Angelia

[ConstMoss]

Thank you Angelia - for the sample data iIve uploaded, this is the desired output.

CustomerId	Inq Date	SaleDate	Channel	Qty
456	     05/05/2016	      06/02/2016	 ACC	 2
123	     03/01/2016	      04/01/2016	 CCE	 1

 @v-huizhn-msft - I want to attribute the latest inquiry to the sale, that is close to the sale date.

[v-huizhn-msft]

Hi @ConstMoss,

Sorry for inconvenience, the file your given is corrupted as follows, could you please share it again? I am unable to download it because of security. Thanks a lot.

Best Regards,
Angelia