Questions about how to combine multiple tables into one visualisation

[at01]

Hello,

I am new to Power BI in general and to Power Query as well. 

I have the following situation: 

3 different tables: Customers, Communication and sales:

Customer:

Customer ID Last name                First Name                       Gender

1	Smith	Linda	F
2	Jones	James	M
3	Spencer	Sharon	F
4	Merty	Larry	M
5	Ball	Stephanie	F

Communication

Communication ID       Message                                     Date Sent             Customer ID

1	Hello Valuable Customer, here are the new arrivals in our store. Check them out.	25/11/2017	3
2	Dear Sharon, new pencils have arrived in the boutique.	15/05/2019	3
3	Sales Coming Up!	11/06/2019	1
4	A new product arrived: the canvas are now in our store	15/09/2020	5

Sales

Invoice ID    Customer ID Date              Store           Item Purchased       Quantity             Unit Price       Currency

1	4	01/03/2017	New York	Book	2	10	USD
2	3	02/12/2017	Sydney	Pen	10	1	AUD
3	1	23/06/2019	London	Book	1	10	GBP
4	3	01/06/2020	London	Pencil	3	2	GBP
5	3	05/10/2020	New York	Canvas	1	30	USD

What I need is to be able to identify if prior to the purchase date a communication has been sent to the client to incite him/her to buy the item.

So what I would need is to be able to combine the Sales and Communication table and have for example the following atatched image.

How should I do this? If I use Merge querries with a left outer join, will this be enough? How to manage the dates to make sure I can use a single timeline?

Any tips on how should I solve this would be greatly appreciated! Thank you!

Alice

[AlB · Accepted solution]

Hi @at01 

I do not understand what you want to do in the chart.

You do not necessarily need to merge any table. You could create a calculated column in the Sales table:

Com sent priot to purchase = 
IF (
    CALCULATE (
        COUNT ( Communication[Date Sent] ),
        FILTER (
            ALL ( Communication[Customer ID] , Communication[Date Sent]),
            Communication[Customer ID] = Sales[Customer ID] &&  Communication[Date Sent] <= Sales[Date])
        )
     > 0,
    TRUE (),
    FALSE ()
)

that will tell you if the com was sent prior to the purchase. You might want to refine this, though, as for example limiting the period you want to check or linking the com type to a specific product.

Note that for Customer 3 there are 3 communications, spanning almost 3 years. So just checking if there's been com sent before the purchase is probably too generic

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers