Forum Discussion
Line Chart - one Date Axis with 2 count measures for different dates in different tables
I have three tables. Cases, Appointments and WO's. The WO table joins to Cases and Appointments (cross filter direction = both) which is how Cases is connected to Appointments. I need to create a count measure using a date field in Cases and one using a Date Field in Appointments and then plot them on one chart in a line graph.
1) Calculate(Count(Case[Case]) by Created Date where type = R or C
2) Calculate (Count(Apppointment[Appointment]) by Due Date where type = R or C and Completed = Y
My thought was to add a date table but I can't seem to get any of my measures to work and I have to make the Date table connection as a dotted line to both tables since I have the WO table connecting to both Case and Appointments.
Here is an example of my tables:
Cases
| Case | Created Date | Type | WO ID |
| 1B | 5/11/2022 | R | 1001 |
| 2B | 4/1/2022 | C | 1002 |
| 3B | 3/1/2022 | R | 1003 |
| 4B | 3/4/2022 | B | 1004 |
| 5B | 4/15/2022 | C | 1005 |
Appointments
| Appointment | Due Date | Completed | Type | WO ID |
| 101 | 5/15/2022 | N | R | 1001 |
| 102 | 5/30/2022 | Y | C | 1002 |
| 103 | 3/15/2022 | Y | R | 1003 |
| 104 | 3/24/2022 | Y | C | 1004 |
| 105 | 4/28/2022 | Y | B | 1005 |
WO
| ID |
| 1001 |
| 1002 |
| 1003 |
| 1004 |
| 1005 |
RESULT
| Case | Appt. Number | |
| May-22 | 1 | 1 |
| Apr-22 | 2 | 0 |
| Mar-22 | 1 | 2 |
3 Replies
speedrampsSuper User
Hi clarkbj71
I want to help but dont understand your description.
Well done on giving example input data in a table format (not a screen shot) which we can import to develop soluutions !!!
Try this and then give a clear description of what you want ....
Click here to download example solution.
Hi Anonymous ,
Here's my soltuion.
1.Create a calendar table. And the relationships are as follows.
Calendar = CALENDAR(DATE(2022,3,1),DATE(2022,5,31))2.Create 2 measure to count.
Count1 = CALCULATE(COUNT('Cases'[WO ID]),FILTER('Cases',[Type] in {"R","C"}))Count2 = CALCULATE(COUNT(Appointments[WO ID]),FILTER('Appointments',[Type] in {"R","C"}&&[Completed]="Y"))You can check more details from my attachment.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
My Data Model is a bit more complicated (this is just a few of the tables in the model but wanted to simplify it for the question). These two tables are currently connected using the WO Table because there are separate graphs using these tables which intereact with each other on the same page... if I keep this connection to WO Table, then I cannot also connect to the DateTable otherwise it would be a cirucular reference. If I remove the connections to the WO Table and then connect to the DateTable, will it not then change those pages and the graphs will no longer be able to interact with each other?