cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
at01
Frequent Visitor

Questions about how to combine multiple tables into one visualisation

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

1SmithLindaF
2JonesJamesM
3SpencerSharonF
4MertyLarryM
5BallStephanieF

 

Communication

Communication ID       Message                                     Date Sent             Customer ID

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

 

Sales

 

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

1401/03/2017New YorkBook210USD
2302/12/2017SydneyPen101AUD
3123/06/2019LondonBook110GBP
4301/06/2020LondonPencil32GBP
5305/10/2020New YorkCanvas130USD

 

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.chart.png

 

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

1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

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 

SU18_powerbi_badge

 

 

View solution in original post

1 REPLY 1
AlB
Super User III
Super User III

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 

SU18_powerbi_badge

 

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.