Line Chart - one Date Axis with 2 count measures for different dates in different tables

[clarkbj71]

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

[Anonymous]

Hi @clarkbj71 ,

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.

[clarkbj71]

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?

[speedramps]

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.