Forum Discussion
Combine Data from Multiple Tables Based on Multiple Dates
***Still Learning Power BI***
I have multiple tables, with 3 or 4 of them being involved in this question. Below is the table breakdown (I can’t provide raw data as I’m using a government system.).
- Contacts
- Call Date
- Customer Number
- Date Table (ChatGPT advised to make one at some point but haven’t used it)
- Date (min-max of queried data)
- Letter Descriptions
- Letter Code
- Description
- Letters
- Letter Date
- Delivery Method
- Customer Number
- Letter Code
- Summary Table (combined data from Letters and Letter Descriptions to summarize how many of each Letter Code were sent each day and via what delivery method)
- Letter Date
- Letter Code
- Code Count
- Delivery Method
- Letter Description
Currently my Report view has the following:
- Summary Table
- Letter Date Slicer
- Letter Code Slicer
- Contacts Table (currently just shows Call Date and Contact Count based on counting Customer Numbers)
- Call Date Slicer
I need to either incorporate into the existing tables, or create a new table, to show a count of customers that called in (Customer Number on Contacts) who received a particular letter (Letter Code on Letters), via a particular delivery method (email/mail), between the selected slicer dates to show what percentage of sent letters created a response within a particular period of time. For example:
- Letter Date Slicer: 11/7/24 – 11/9/24
- Call Date Slicer: 11/14/24 – 11/15/24
Letter Date | Letter Code | Letter Count | Delivery Method | Customer Contacts | Contact Rate |
11/7/24 | A123 | 1000 | 180 | 18% | |
11/7/24 | A123 | 1000 | 50 | 5% | |
11/8/24 | B234 | 1000 | 200 | 20% | |
11/9/24 | C345 | 1000 | 100 | 10% |
Hi Cellblime ,
I create sample datas myself:
And I use this DAX to create the Date Table:Call Date Slicer = CALENDAR(DATE(2024, 11, 1), TODAY())Use these DAXs to create three measures:
Letter Count = COUNTROWS('Letters')Customer Contacts = VAR _Lettercode = MAX('Letters'[Letter Code]) VAR _Customernumber = CALCULATETABLE( VALUES(Letters[Customer Number]), 'Letters'[Letter Code] = _Lettercode ) RETURN CALCULATE( COUNT('Contacts'[Customer Number]), 'Contacts'[Call Date] >= MIN('Call Date Slicer'[Call Date Slicer]) && 'Contacts'[Call Date] <= MAX('Call Date Slicer'[Call Date Slicer]) && 'Contacts'[Customer Number] IN _Customernumber )Contact Rate = [Customer Contacts] / [Letter Count]Use column Letter Date in Table Letters to create letter date slicer:
Use column Call Date Slicer in Table Date to create call date slicer:
And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Cellblime ,
I create sample datas myself:
And I use this DAX to create the Date Table:Call Date Slicer = CALENDAR(DATE(2024, 11, 1), TODAY())Use these DAXs to create three measures:
Letter Count = COUNTROWS('Letters')Customer Contacts = VAR _Lettercode = MAX('Letters'[Letter Code]) VAR _Customernumber = CALCULATETABLE( VALUES(Letters[Customer Number]), 'Letters'[Letter Code] = _Lettercode ) RETURN CALCULATE( COUNT('Contacts'[Customer Number]), 'Contacts'[Call Date] >= MIN('Call Date Slicer'[Call Date Slicer]) && 'Contacts'[Call Date] <= MAX('Call Date Slicer'[Call Date Slicer]) && 'Contacts'[Customer Number] IN _Customernumber )Contact Rate = [Customer Contacts] / [Letter Count]Use column Letter Date in Table Letters to create letter date slicer:
Use column Call Date Slicer in Table Date to create call date slicer:
And the final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello. Thank you for putting in the time to work through this request. I couldn't get too far into your suggestion as my data will always have duplicates so I can't establish relationships (customer calls multiple times, multiple letters sent to same customer, etc.).
I bypassed the relationship part and it seems like I was able to get it worked out, or at least it appears that way. I'm going to reconcile the tables against the raw data and see if it worked for sure. I'm grateful and will kudos/accept solution as soon as I verify. Thanks so much!
