Forum Discussion
Get value from other table if
Hi,
I'm new to PowerBI and my brain usually in the Excel realm. I have two datasets which shows (1) each response to a message and (2) when the invite was sent. My ultimate goal is to be able to see when we have most success inviting people to respond with hypotheses like "it's best to invite on fridays", "we should wait longer between our reminders to get the answers we want" and so on. My date format is "dd.mm.yyyy hh.mm.ss"
Table 1
| Project | Costumer ID | Response date/time | Type of message |
| A | 1 | 01.01.2022 12:00 | Main Invite |
| A | 2 | 02.01.2022 13:00 | Main Invite |
| A | 3 | 5.01.2022 11:00 | Reminder 1 |
| B | 4 | 10.02.2022 10:00 | Main Invite |
| B | 5 | 14.02.2022 11:30 | Main Invite |
Table 2 - When invites/reminers are sent and number of people reached
| Project | Date | Type | Reach | Number of people responded within dates |
| A | 01.01.2022 15:00 | Main invite | 100 000 | 62 000 |
| A | 04.01.2022 12:00 | Reminder 1 | 38 000 | 15 000 |
| B | 08.01.2022 10:00 | Main invite | 50 000 | 25 000 |
Within Excel I would probably have added a column to table 1 stating what kind of invite/reminder the costumer responded to (most likely). Since the reminder goes out to all those who didn't respond, I would have made a column in table 2 showing how many responded because of the invite/reminder. I cannot seem to get how to add these columns based on what project it is and if the date of response is between dates in table 2.
Not even sure if this is a good idea so please help me understand!
3 Replies
Please show the expected result given the data you present.
Hi, thank you for replying! Sorry for the late response. I made an example with similar data. I want to show, among other things, how many responses I have recieved with the different messages. Something like this:
To maybe simplify things a bit: What I need is how to create the relationship between the tables so that I can study when my respondents reply and to what message I sent out to them. Let's say I send out an email to 100 people, I get 60 responses within the two first days. I then send out a reminder to the remaining 40 people and 25 reply. I then close the possibility for replying on that particular topic. My result is in two tables:
Type of email Number of people sent to Date Main 100 1. january 2022 Reminder 40 3. january 2022 Respondent-ID Date reply 1 1. january 2022 2 1. january 2022 3 2. january 2022 ... 61 3. january 2022 Every reply from 3rd of January is considered to be a reply to the reminder, not the first email (I know that is not certain, but that doesn't matter). So how do I combine these so that I can produce charts such as shown in the previous message?
