Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I'm currently working on an email dashboard and want to analyze if an email leads to an order.
The order is assigned to the email if the customer placed an order in three days.
I have two tables the email table with al the emails send out to the customers and the order table with al the order details. Now I want to make a relationship between both tables, but this is not possible on send_date and order_date. The values are not the same. How can I make the relationship in such a way that it can take orders of three days into account?
Email_table:
email_campaign || Email || Send_date || Click
target_email || piet@gmail.com || 21-5-2018 || 1
order_table:
Email || Ordernummer || Order_date || order_value
piet@gmail.com|| X108 || 22-5-2018 || 20
End result:
email_campaign || Email || Send_date || Click || order_date || order_value
target_email || piet@gmail.com || 21-5-2018 || 1 || 22-5-2018 || 20
Solved! Go to Solution.
Hi,
@TomEluscious wrote:Thank you for your explanation. In this example you are right, but what if I a customer received another target email. Then the email adress is not unique anymore?
Email_table:
email_campaign || Email || Send_date || Click
target_email_1 || piet@gmail.com || 21-5-2018 || 1
target_email_2 || piet@gmail.com || 24-5-2018 || 1
order_table:
Email || Ordernummer || Order_date || order_value
piet@gmail.com|| X108 || 22-5-2018 || 20
piet@gmail.com|| X120 || 25-5-2018 || 26
End result:
email_campaign || Email || Send_date || Click || order_date || order_value
target_email_1 || piet@gmail.com || 21-5-2018 || 1 || 22-5-2018 || 20
target_email_2 || piet@gmail.com || 24-5-2018 || 1 || 25-5-2018 || 26
In this case where you will have multiple emails in both the tables, you can add a bridge table between these tables that will have all the unique Emails in it and join the Order and Email table using this bridge table. The Modle would look something like the one in the below screenshot
In the above screenshot, Date column in Table1(2) will have unique dates. In your case, this will be all unique email Ids
Is there actually anything unique within your data model that can be used to connect the two tables?
Hi,
Join the tables based on Email Column
FYR, screenshot below
Note: The Image shows a 1:1 relationship. It there is a many to one relationship, correspondingly the model has to be changes
Thank you for your explanation. In this example you are right, but what if I a customer received another target email. Then the email adress is not unique anymore?
Email_table:
email_campaign || Email || Send_date || Click
target_email_1 || piet@gmail.com || 21-5-2018 || 1
target_email_2 || piet@gmail.com || 24-5-2018 || 1
order_table:
Email || Ordernummer || Order_date || order_value
piet@gmail.com|| X108 || 22-5-2018 || 20
piet@gmail.com|| X120 || 25-5-2018 || 26
End result:
email_campaign || Email || Send_date || Click || order_date || order_value
target_email_1 || piet@gmail.com || 21-5-2018 || 1 || 22-5-2018 || 20
target_email_2 || piet@gmail.com || 24-5-2018 || 1 || 25-5-2018 || 26
Hi,
@TomEluscious wrote:Thank you for your explanation. In this example you are right, but what if I a customer received another target email. Then the email adress is not unique anymore?
Email_table:
email_campaign || Email || Send_date || Click
target_email_1 || piet@gmail.com || 21-5-2018 || 1
target_email_2 || piet@gmail.com || 24-5-2018 || 1
order_table:
Email || Ordernummer || Order_date || order_value
piet@gmail.com|| X108 || 22-5-2018 || 20
piet@gmail.com|| X120 || 25-5-2018 || 26
End result:
email_campaign || Email || Send_date || Click || order_date || order_value
target_email_1 || piet@gmail.com || 21-5-2018 || 1 || 22-5-2018 || 20
target_email_2 || piet@gmail.com || 24-5-2018 || 1 || 25-5-2018 || 26
In this case where you will have multiple emails in both the tables, you can add a bridge table between these tables that will have all the unique Emails in it and join the Order and Email table using this bridge table. The Modle would look something like the one in the below screenshot
In the above screenshot, Date column in Table1(2) will have unique dates. In your case, this will be all unique email Ids
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |